ij> --
--   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);
0 rows inserted/updated/deleted
ij> insert into blob values(3);
1 row inserted/updated/deleted
ij> select blob.a from blob;
A          
-----------
3          
ij> create table clob(a int);
0 rows inserted/updated/deleted
ij> insert into clob values(3);
1 row inserted/updated/deleted
ij> select clob.a from clob;
A          
-----------
3          
ij> create table nclob(a int);
0 rows inserted/updated/deleted
ij> insert into nclob values(3);
1 row inserted/updated/deleted
ij> select nclob.a from nclob;
A          
-----------
3          
ij> create table a(blob int, clob int, nclob int);
0 rows inserted/updated/deleted
ij> insert into a values(1,2,3);
1 row inserted/updated/deleted
ij> insert into a(blob, clob, nclob) values(1,2,3);
1 row inserted/updated/deleted
ij> select a.blob, a.clob, a.nclob from a;
BLOB       |CLOB       |NCLOB      
-----------------------------------
1          |2          |3          
1          |2          |3          
ij> select a.blob, a.clob, a.nclob from a where a.blob = 1;
BLOB       |CLOB       |NCLOB      
-----------------------------------
1          |2          |3          
1          |2          |3          
ij> select a.blob, a.clob, a.nclob from a where a.clob = 2;
BLOB       |CLOB       |NCLOB      
-----------------------------------
1          |2          |3          
1          |2          |3          
ij> select a.blob, a.clob, a.nclob from a where a.nclob = 3;
BLOB       |CLOB       |NCLOB      
-----------------------------------
1          |2          |3          
1          |2          |3          
ij> select a.blob, a.clob, a.nclob from a where a.blob = 1 and a.clob = 2 and a.nclob = 3;
BLOB       |CLOB       |NCLOB      
-----------------------------------
1          |2          |3          
1          |2          |3          
ij> create table b(blob blob(3K), clob clob(2M));
0 rows inserted/updated/deleted
ij> insert into b values(cast(X'0031' as blob(3K)),cast('2' as clob(2M)));
1 row inserted/updated/deleted
ij> insert into b(blob, clob, nclob) values(cast(X'0031' as blob(3K)),cast('2' as clob(2M)));
ERROR 42X14: 'NCLOB' is not a column in table or VTI 'APP.B'.
ij> select b.blob, b.clob, b.nclob from b;
ERROR 42X04: Column 'B.NCLOB' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'B.NCLOB' is not a column in the target table.
ij> -- equal tests are not allowed
select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(5));
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(7));
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(7));
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(7));
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast(X'e0' as blob(5))=cast(X'e000' as blob(7));
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where X'80' = cast(X'80' as blob(1));
ERROR 42818: Comparisons between 'CHAR () FOR BIT DATA' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast(X'80' as blob(1)) = X'80';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR () FOR BIT DATA' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast(X'80' as blob(1)) = cast(X'80' as blob(1));
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where '1' = cast('1' as clob(1));
ERROR 42818: Comparisons between 'CHAR (UCS_BASIC)' and 'CLOB (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast('1' as clob(1)) = '1';
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast('1' as clob(1)) = cast('1' as clob(1));
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CLOB (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where '1' = cast('1' as nclob(1));
ERROR 0A000: Feature not implemented: NCLOB.
ij> select 1 from b where cast('1' as nclob(1)) = '1';
ERROR 0A000: Feature not implemented: NCLOB.
ij> select 1 from b where cast('1' as nclob(1)) = cast('1' as nclob(1));
ERROR 0A000: Feature not implemented: NCLOB.
ij> -- NCLOB is comparable with CLOB

select 1 from b where cast('1' as nclob(10)) = cast('1' as clob(10));
ERROR 0A000: Feature not implemented: NCLOB.
ij> select 1 from b where cast('1' as clob(10)) = cast('1' as nclob(10));
ERROR 0A000: Feature not implemented: NCLOB.
ij> -- comparsion using tables
select * from b as b1, b as b2 where b1.blob=b2.blob;
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from b as b1, b as b2 where b1.blob!=b2.blob;
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from b as b1, b as b2 where b1.blob=X'20';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR () FOR BIT DATA' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from b as b1, b as b2 where X'20'=b1.blob;
ERROR 42818: Comparisons between 'CHAR () FOR BIT DATA' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from b as b1, b as b2 where X'20'!=b1.blob;
ERROR 42818: Comparisons between 'CHAR () FOR BIT DATA' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from b as b1, b as b2 where b1.blob=X'7575';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR () FOR BIT DATA' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from b as b1, b as b2 where X'7575'=b1.blob;
ERROR 42818: Comparisons between 'CHAR () FOR BIT DATA' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select b.blob, b.clob, b.nclob from b where b.blob = '1' and b.clob = '2' and b.nclob = '3';
ERROR 42X04: Column 'B.NCLOB' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'B.NCLOB' is not a column in the target table.
ij> select b.blob from b where b.blob = '1';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> -- however it works for types which cloudscape autocasts to char
select b.clob from b where b.clob = '2';
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select b.nclob from b where b.nclob = '3';
ERROR 42X04: Column 'B.NCLOB' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'B.NCLOB' is not a column in the target table.
ij> -- test insert of NULL
insert into b values(null, null, null);
ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns.
ij> select * from b;
BLOB                                                                                                                            |CLOB                                                                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0031                                                                                                                            |2                                                                                                                               
ij> -- cleanup
drop table blob;
0 rows inserted/updated/deleted
ij> drop table clob;
0 rows inserted/updated/deleted
ij> drop table nclob;
0 rows inserted/updated/deleted
ij> drop table a;
0 rows inserted/updated/deleted
ij> drop table b;
0 rows inserted/updated/deleted
ij> -- test insert limitations
create table b(b blob(5));
0 rows inserted/updated/deleted
ij> create table c(c clob(5));
0 rows inserted/updated/deleted
ij> create table n(n nclob(5));
ERROR 0A000: Feature not implemented: NCLOB.
ij> insert into b values(cast(X'01020304' as blob(10)));
1 row inserted/updated/deleted
ij> insert into b values(cast(X'0102030405' as blob(10)));
1 row inserted/updated/deleted
ij> insert into b values(cast(X'010203040506' as blob(10)));
ERROR 22001: A truncation error was encountered trying to shrink BLOB 'XXXX' to length 5.
ij> -- truncate before insert, no errors
insert into b values(cast(X'01020304' as blob(5)));
1 row inserted/updated/deleted
ij> insert into b values(cast(X'0102030405' as blob(5)));
1 row inserted/updated/deleted
ij> insert into b values(cast(X'010203040506' as blob(5)));
1 row inserted/updated/deleted
WARNING 01004: Data truncation
ij> -- clob/nclob
--   ok in spite of not being cast
insert into c values('1234');
1 row inserted/updated/deleted
ij> insert into c values('12345');
1 row inserted/updated/deleted
ij> insert into c values('123456');
ERROR 22001: A truncation error was encountered trying to shrink CLOB '123456' to length 5.
ij> insert into n values('1234');
ERROR 42X05: Table/View 'N' does not exist.
ij> insert into n values('12345');
ERROR 42X05: Table/View 'N' does not exist.
ij> insert into n values('123456');
ERROR 42X05: Table/View 'N' does not exist.
ij> --   ok
insert into c values(cast('1234' as clob(5)));
1 row inserted/updated/deleted
ij> insert into c values(cast('12345' as clob(5)));
1 row inserted/updated/deleted
ij> insert into c values(cast('123456' as clob(5)));
1 row inserted/updated/deleted
WARNING 01004: Data truncation
ij> insert into n values(cast('1234' as nclob(5)));
ERROR 0A000: Feature not implemented: NCLOB.
ij> insert into n values(cast('12345' as nclob(5)));
ERROR 0A000: Feature not implemented: NCLOB.
ij> insert into n values(cast('123456' as nclob(5)));
ERROR 0A000: Feature not implemented: NCLOB.
ij> select * from b;
B         
----------
01020304  
0102030405
01020304  
0102030405
0102030405
ij> select * from c;
C    
-----
1234 
12345
1234 
12345
12345
ij> select * from n;
ERROR 42X05: Table/View 'N' does not exist.
ij> -- concatenate
values cast('12' as clob(2)) || cast('34' as clob(2));
1   
----
1234
ij> values cast('12' as nclob(2)) || cast('34' as nclob(2));
ERROR 0A000: Feature not implemented: NCLOB.
ij> select 1 from b where cast('12' as clob(2)) || cast('34' as clob(2)) = '1234';
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast('12' as nclob(2)) || cast('34' as nclob(2)) = '1234';
ERROR 0A000: Feature not implemented: NCLOB.
ij> select 1 from b where cast('12' as clob(2)) || cast('34' as clob(2)) = cast('1234' as clob(4));
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CLOB (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast('12' as nclob(2)) || cast('34' as nclob(2)) = cast('1234' as clob(4));
ERROR 0A000: Feature not implemented: NCLOB.
ij> -- like
select * from b where b like '0102%';
ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION' having compatible arguments was found.
ij> select * from c where c like '12%';
C    
-----
1234 
12345
1234 
12345
12345
ij> select * from n where n like '12%';
ERROR 42X05: Table/View 'N' does not exist.
ij> select * from b where b like cast('0102%' as blob(10));
ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
ij> select * from c where c like cast('12%' as clob(10));
C    
-----
1234 
12345
1234 
12345
12345
ij> select * from n where n like cast('12%' as nclob(10));
ERROR 0A000: Feature not implemented: NCLOB.
ij> -- cleanup
drop table b;
0 rows inserted/updated/deleted
ij> drop table c;
0 rows inserted/updated/deleted
ij> drop table n;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'N' because it does not exist.
ij> -- test syntax of using long type names
create table a(a binary large object(3K));
0 rows inserted/updated/deleted
ij> create table b(a character large object(3K));
0 rows inserted/updated/deleted
ij> create table c(a national character large object(3K));
ERROR 0A000: Feature not implemented: NCLOB.
ij> create table d(a char large object(204K));
0 rows inserted/updated/deleted
ij> -- create index (not allowed)
create index ia on a(a);
ERROR X0X67: Columns of type 'BLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> create index ib on b(a);
ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> create index ic on c(a);
ERROR 42Y55: 'CREATE INDEX' cannot be performed on 'C' because it does not exist.
ij> create index id on d(a);
ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> -- cleanup
drop table a;
0 rows inserted/updated/deleted
ij> drop table c;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'C' because it does not exist.
ij> drop table d;
0 rows inserted/updated/deleted
ij> -- ORDER tests on LOB types (not allowed)
select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(5));
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast(X'e0' as blob(5))!=cast(X'e0' as blob(5));
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast(X'e0' as blob(5))<cast(X'e0' as blob(5));
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast(X'e0' as blob(5))>cast(X'e0' as blob(7));
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast(X'e0' as blob(5))<=cast(X'e0' as blob(7));
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast(X'e0' as blob(5))>=cast(X'e0' as blob(7));
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast('fish' as clob(5))=cast('fish' as clob(5));
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CLOB (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast('fish' as clob(5))!=cast('fish' as clob(5));
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CLOB (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast('fish' as clob(5))<cast('fish' as clob(5));
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CLOB (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast('fish' as clob(5))>cast('fish' as clob(7));
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CLOB (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast('fish' as clob(5))<=cast('fish' as clob(7));
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CLOB (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast('fish' as clob(5))>=cast('fish' as clob(7));
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CLOB (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select 1 from b where cast('fish' as nclob(5))=cast('fish' as nclob(5));
ERROR 0A000: Feature not implemented: NCLOB.
ij> select 1 from b where cast('fish' as nclob(5))!=cast('fish' as nclob(5));
ERROR 0A000: Feature not implemented: NCLOB.
ij> select 1 from b where cast('fish' as nclob(5))<cast('fish' as nclob(5));
ERROR 0A000: Feature not implemented: NCLOB.
ij> select 1 from b where cast('fish' as nclob(5))>cast('fish' as nclob(7));
ERROR 0A000: Feature not implemented: NCLOB.
ij> select 1 from b where cast('fish' as nclob(5))<=cast('fish' as nclob(7));
ERROR 0A000: Feature not implemented: NCLOB.
ij> select 1 from b where cast('fish' as nclob(5))>=cast('fish' as nclob(7));
ERROR 0A000: Feature not implemented: NCLOB.
ij> -- 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));
0 rows inserted/updated/deleted
ij> INSERT INTO testoperatorclob VALUES (CAST('50' AS CLOB(1K)));
1 row inserted/updated/deleted
ij> INSERT INTO testoperatorclob VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K)));
1 row inserted/updated/deleted
ij> select * from testoperatorclob;
COLONE                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------------
50                                                                                                                              
50                                                                                                                              
ij> -- these select statements should raise an error but are successful in cloudscape
select * from testoperatorclob where colone > 10;
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorclob where colone > 5;
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorclob where colone < 70;
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorclob where colone = 50;
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorclob where colone != 10;
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorclob where colone <= 70;
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorclob where colone >= 10;
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorclob where colone <> 10;
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorclob where colone > '10';
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorclob where colone > '5';
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorclob where colone < '70';
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorclob where colone = '50';
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorclob where colone != '10';
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorclob where colone <= '70';
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorclob where colone >= '10';
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorclob where colone <> '10';
ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> drop table testoperatorclob;
0 rows inserted/updated/deleted
ij> -- BLOB testing
CREATE TABLE testoperatorblob (colone blob(1K));
0 rows inserted/updated/deleted
ij> INSERT INTO testoperatorblob VALUES (CAST('50' AS BLOB(1K)));
ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
ij> INSERT INTO testoperatorblob VALUES (CAST(cast('50' as varchar(80)) AS BLOB(1K)));
ERROR 42846: Cannot convert types 'VARCHAR' to 'BLOB'.
ij> select * from testoperatorblob;
COLONE                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------------
ij> -- these select statements should raise an error but are successful in cloudscape
select * from testoperatorblob where colone > 10;
ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorblob where colone > 5;
ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorblob where colone < 999999;
ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorblob where colone = 00350030;
ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorblob where colone != 10;
ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorblob where colone <= 999999;
ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorblob where colone >= 10;
ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorblob where colone <> 10;
ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorblob where colone > '10';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorblob where colone > '5';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorblob where colone < '70';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorblob where colone = '50';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorblob where colone != '10';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorblob where colone <= '70';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorblob where colone >= '10';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testoperatorblob where colone <> '10';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> drop table testoperatorblob;
0 rows inserted/updated/deleted
ij> -- NCLOB testing
CREATE TABLE testoperatornclob (colone nclob(1K));
ERROR 0A000: Feature not implemented: NCLOB.
ij> INSERT INTO testoperatornclob VALUES (CAST('50' AS NCLOB(1K)));
ERROR 0A000: Feature not implemented: NCLOB.
ij> INSERT INTO testoperatornclob VALUES (CAST(cast('50' as varchar(80)) AS NCLOB(1K)));
ERROR 0A000: Feature not implemented: NCLOB.
ij> select * from testoperatornclob;
ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist.
ij> -- these select statements should raise an error but are successful in cloudscape
select * from testoperatornclob where colone > 10;
ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone > 5;
ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone < 70;
ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone = 50;
ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone != 10;
ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone <= 70;
ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone >= 10;
ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone <> 10;
ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone > '10';
ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone > '5';
ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone < '70';
ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone = '50';
ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone != '10';
ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone <= '70';
ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone >= '10';
ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone <> '10';
ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist.
ij> drop table testoperatornclob;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TESTOPERATORNCLOB' because it does not exist.
ij> ----- test method invocations on LOB objects (should disallow)
-- setup
drop table b;
0 rows inserted/updated/deleted
ij> create table b(b blob(77));
0 rows inserted/updated/deleted
ij> insert into b values(cast('33' as blob(77)));
ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
ij> create table c(c clob(77));
0 rows inserted/updated/deleted
ij> insert into c values(cast('33' as clob(77)));
1 row inserted/updated/deleted
ij> -- LOB as main object for method invocation not allowed
values (cast('1' as blob(1M)))->toString();
ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
ij> values (cast('1' as clob(1M)))->toString();
ERROR XJ082: BLOB/CLOB values are not allowed as method parameters or receiver.
ij> values (cast('1' as nclob(1M)))->toString();
ERROR 0A000: Feature not implemented: NCLOB.
ij> drop table b;
0 rows inserted/updated/deleted
ij> drop table c;
0 rows inserted/updated/deleted
ij> ------ TEST length functions on LOBs
---- BLOB
values length(cast('foo' as blob(10)));
ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
ij> values {fn length(cast('foo' as blob(10)))};
ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
ij> ---- CHAR
values length(cast('foo' as char(10)));
1          
-----------
10         
ij> values {fn length(cast('foo' as char(10)))};
1          
-----------
3          
ij> ---- CLOB
values length(cast('foo' as clob(10)));
1          
-----------
3          
ij> values {fn length(cast('foo' as clob(10)))};
1          
-----------
3          
ij> ---- NCLOB
values length(cast('foo' as nclob(10)));
ERROR 0A000: Feature not implemented: NCLOB.
ij> values {fn length(cast('foo' as nclob(10)))};
ERROR 0A000: Feature not implemented: NCLOB.
ij> -- Longvarchar negative tests

create table testPredicate1 (c1 long varchar);
0 rows inserted/updated/deleted
ij> create table testPredicate2 (c1 long varchar);
0 rows inserted/updated/deleted
ij> insert into testPredicate1 (c1) values 'a';
1 row inserted/updated/deleted
ij> insert into testPredicate2 (c1) values 'a';
1 row inserted/updated/deleted
ij> -- UNION
select * from testPredicate1 union select * from testPredicate2;
ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> -- IN predicate
select c1 from testPredicate1 where c1 IN (select c1 from testPredicate2);
ERROR 42818: Comparisons between 'LONG VARCHAR (UCS_BASIC)' and 'LONG VARCHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> -- NOT IN predicate
select c1 from testPredicate1 where c1 NOT IN (select c1 from testPredicate2);
ERROR 42818: Comparisons between 'LONG VARCHAR (UCS_BASIC)' and 'LONG VARCHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> -- ORDER BY clause
select * from testPredicate1 order by c1;
ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> -- GROUP BY clause
select substr(c1,1,2) from testPredicate1 group by c1;
ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> -- JOIN
select * from testPredicate1 t1, testPredicate2 t2 where t1.c1=t2.c1;
ERROR 42818: Comparisons between 'LONG VARCHAR (UCS_BASIC)' and 'LONG VARCHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select * from testPredicate1 LEFT OUTER JOIN testPredicate2 on testPredicate1.c1=testPredicate2.c1;
ERROR 42818: Comparisons between 'LONG VARCHAR (UCS_BASIC)' and 'LONG VARCHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> -- PRIMARY KEY
create table testConst1(c1 long varchar not null primary key);
ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> -- UNIQUE KEY constraints
CREATE TABLE testconst2 (col1 long varchar not null, CONSTRAINT uk UNIQUE (col1));
ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> -- FOREIGN KEY constraints
create table testConst3 (c1 char(10) not null, primary key (c1));
0 rows inserted/updated/deleted
ij> create table testConst4 (c1 long varchar not null, constraint fk foreign key (c1) references testConst3 (c1));
ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> drop table testConst3;
0 rows inserted/updated/deleted
ij> -- MAX aggregate function
select max(c1) from testPredicate1;
ERROR 42Y22: Aggregate MAX cannot operate on type LONG VARCHAR.
ij> -- MIN aggregate function
select min(c1) from testPredicate1;
ERROR 42Y22: Aggregate MIN cannot operate on type LONG VARCHAR.
ij> drop table testpredicate1;
0 rows inserted/updated/deleted
ij> drop table testpredicate2;
0 rows inserted/updated/deleted
ij> -- CLOB/BLOB limits and sizes

-- FAIL - bigger than 2G or 2Gb with no modifier
create table DB2LIM.FB1(FB1C BLOB(3G));
ERROR 42X44: Invalid length '3G' in column specification.
ij> create table DB2LIM.FB2(FB2C BLOB(2049M));
ERROR 42X44: Invalid length '2049M' in column specification.
ij> create table DB2LIM.FB3(FB3C BLOB(2097153K));
ERROR 42X44: Invalid length '2097153K' in column specification.
ij> create table DB2LIM.FB4(FB4C BLOB(2147483648));
ERROR 42X44: Invalid length '2147483648' in column specification.
ij> -- OK 2G and end up as 2GB - 1 (with modifier)
create table DB2LIM.GB1(GB1C BLOB(2G));
0 rows inserted/updated/deleted
ij> create table DB2LIM.GB2(GB2C BLOB(2048M));
0 rows inserted/updated/deleted
ij> create table DB2LIM.GB3(GB3C BLOB(2097152K));
0 rows inserted/updated/deleted
ij> create table DB2LIM.GB4(GB4C BLOB(2147483647));
0 rows inserted/updated/deleted
ij> -- next lower value
create table DB2LIM.GB5(GB5C BLOB(1G));
0 rows inserted/updated/deleted
ij> create table DB2LIM.GB6(GB6C BLOB(2047M));
0 rows inserted/updated/deleted
ij> create table DB2LIM.GB7(GB7C BLOB(2097151K));
0 rows inserted/updated/deleted
ij> create table DB2LIM.GB8(GB8C BLOB(2147483646));
0 rows inserted/updated/deleted
ij> drop table DB2LIM.GB5;
0 rows inserted/updated/deleted
ij> drop table DB2LIM.GB6;
0 rows inserted/updated/deleted
ij> drop table DB2LIM.GB7;
0 rows inserted/updated/deleted
ij> drop table DB2LIM.GB8;
0 rows inserted/updated/deleted
ij> -- no length (default to 1Mb)
create table DB2LIM.GB9(GB9C BLOB);
0 rows inserted/updated/deleted
ij> create table DB2LIM.GB10(GB10C BINARY LARGE OBJECT);
0 rows inserted/updated/deleted
ij> drop table DB2LIM.GB9;
0 rows inserted/updated/deleted
ij> drop table DB2LIM.GB10;
0 rows inserted/updated/deleted
ij> -- FAIL - bigger than 2G or 2Gb with no modifier
create table DB2LIM.FC1(FC1C CLOB(3G));
ERROR 42X44: Invalid length '3G' in column specification.
ij> create table DB2LIM.FC2(FC2C CLOB(2049M));
ERROR 42X44: Invalid length '2049M' in column specification.
ij> create table DB2LIM.FC3(FC3C CLOB(2097153K));
ERROR 42X44: Invalid length '2097153K' in column specification.
ij> create table DB2LIM.FC4(FC4C CLOB(2147483648));
ERROR 42X44: Invalid length '2147483648' in column specification.
ij> -- OK 2G and end up as 2GC - 1 (with modifier)
create table DB2LIM.GC1(GC1C CLOB(2G));
0 rows inserted/updated/deleted
ij> create table DB2LIM.GC2(GC2C CLOB(2048M));
0 rows inserted/updated/deleted
ij> create table DB2LIM.GC3(GC3C CLOB(2097152K));
0 rows inserted/updated/deleted
ij> create table DB2LIM.GC4(GC4C CLOB(2147483647));
0 rows inserted/updated/deleted
ij> -- next lower value
create table DB2LIM.GC5(GC5C CLOB(1G));
0 rows inserted/updated/deleted
ij> create table DB2LIM.GC6(GC6C CLOB(2047M));
0 rows inserted/updated/deleted
ij> create table DB2LIM.GC7(GC7C CLOB(2097151K));
0 rows inserted/updated/deleted
ij> create table DB2LIM.GC8(GC8C CLOB(2147483646));
0 rows inserted/updated/deleted
ij> drop table DB2LIM.GC5;
0 rows inserted/updated/deleted
ij> drop table DB2LIM.GC6;
0 rows inserted/updated/deleted
ij> drop table DB2LIM.GC7;
0 rows inserted/updated/deleted
ij> drop table DB2LIM.GC8;
0 rows inserted/updated/deleted
ij> -- no length (default to 1Mb)
create table DB2LIM.GC9(GC9C CLOB);
0 rows inserted/updated/deleted
ij> create table DB2LIM.GC10(GC10C CHARACTER LARGE OBJECT);
0 rows inserted/updated/deleted
ij> create table DB2LIM.GC11(GC11C CHAR LARGE OBJECT);
0 rows inserted/updated/deleted
ij> drop table DB2LIM.GC9;
0 rows inserted/updated/deleted
ij> drop table DB2LIM.GC10;
0 rows inserted/updated/deleted
ij> drop table DB2LIM.GC11;
0 rows inserted/updated/deleted
ij> 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;
T         |C         |Y                             
----------------------------------------------------
GB1       |GB1C      |BLOB(2147483647)              
GB2       |GB2C      |BLOB(2147483647)              
GB3       |GB3C      |BLOB(2147483647)              
GB4       |GB4C      |BLOB(2147483647)              
GC1       |GC1C      |CLOB(2147483647)              
GC2       |GC2C      |CLOB(2147483647)              
GC3       |GC3C      |CLOB(2147483647)              
GC4       |GC4C      |CLOB(2147483647)              
ij> --- CHAR/VARCHAR and LOBs. (beetle 5741)
--- test that we can insert CHAR/VARCHAR directly

CREATE TABLE b (colone blob(1K));
0 rows inserted/updated/deleted
ij> VALUES '50';
1 
--
50
ij> INSERT INTO b VALUES '50';
ERROR 42821: Columns of type 'BLOB' cannot hold values of type 'CHAR'. 
ij> VALUES cast('50' as varchar(80));
1                                                                               
--------------------------------------------------------------------------------
50                                                                              
ij> INSERT INTO b VALUES cast('50' as varchar(80));
ERROR 42821: Columns of type 'BLOB' cannot hold values of type 'VARCHAR'. 
ij> VALUES (CAST('50' AS BLOB(1K)));
ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
ij> INSERT INTO b VALUES (CAST('50' AS BLOB(1K)));
ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
ij> VALUES (CAST(cast('50' as varchar(80)) AS BLOB(1K)));
ERROR 42846: Cannot convert types 'VARCHAR' to 'BLOB'.
ij> INSERT INTO b VALUES (CAST(cast('50' as varchar(80)) AS BLOB(1K)));
ERROR 42846: Cannot convert types 'VARCHAR' to 'BLOB'.
ij> VALUES cast('50' as long varchar);
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
50                                                                                                                              
ij> INSERT INTO b VALUES cast('50' as long varchar);
ERROR 42821: Columns of type 'BLOB' cannot hold values of type 'LONG VARCHAR'. 
ij> -- test w LOBs
VALUES (CAST('50' AS BLOB(1K)));
ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
ij> INSERT INTO b VALUES (CAST('50' AS BLOB(1K)));
ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
ij> VALUES (CAST('50' AS CLOB(1K)));
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
50                                                                                                                              
ij> INSERT INTO b VALUES (CAST('50' AS CLOB(1K)));
ERROR 42821: Columns of type 'BLOB' cannot hold values of type 'CLOB'. 
ij> VALUES (CAST('50' AS NCLOB(1K)));
ERROR 0A000: Feature not implemented: NCLOB.
ij> INSERT INTO b VALUES (CAST('50' AS NCLOB(1K)));
ERROR 0A000: Feature not implemented: NCLOB.
ij> DROP TABLE b;
0 rows inserted/updated/deleted
ij> CREATE TABLE c (colone clob(1K));
0 rows inserted/updated/deleted
ij> VALUES '50';
1 
--
50
ij> INSERT INTO c VALUES '50';
1 row inserted/updated/deleted
ij> VALUES cast('50' as varchar(80));
1                                                                               
--------------------------------------------------------------------------------
50                                                                              
ij> INSERT INTO c VALUES cast('50' as varchar(80));
1 row inserted/updated/deleted
ij> VALUES (CAST('50' AS CLOB(1K)));
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
50                                                                                                                              
ij> INSERT INTO c VALUES (CAST('50' AS CLOB(1K)));
1 row inserted/updated/deleted
ij> VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K)));
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
50                                                                                                                              
ij> INSERT INTO c VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K)));
1 row inserted/updated/deleted
ij> VALUES cast('50' as long varchar);
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
50                                                                                                                              
ij> INSERT INTO c VALUES cast('50' as long varchar);
1 row inserted/updated/deleted
ij> -- test w LOBs
VALUES (CAST('50' AS BLOB(1K)));
ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
ij> INSERT INTO c VALUES (CAST('50' AS BLOB(1K)));
ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
ij> VALUES (CAST('50' AS CLOB(1K)));
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
50                                                                                                                              
ij> INSERT INTO c VALUES (CAST('50' AS CLOB(1K)));
1 row inserted/updated/deleted
ij> VALUES (CAST('50' AS NCLOB(1K)));
ERROR 0A000: Feature not implemented: NCLOB.
ij> INSERT INTO c VALUES (CAST('50' AS NCLOB(1K)));
ERROR 0A000: Feature not implemented: NCLOB.
ij> DROP TABLE c;
0 rows inserted/updated/deleted
ij> CREATE TABLE n (colone clob(1K));
0 rows inserted/updated/deleted
ij> VALUES '50';
1 
--
50
ij> INSERT INTO n VALUES '50';
1 row inserted/updated/deleted
ij> VALUES cast('50' as varchar(80));
1                                                                               
--------------------------------------------------------------------------------
50                                                                              
ij> INSERT INTO n VALUES cast('50' as varchar(80));
1 row inserted/updated/deleted
ij> VALUES (CAST('50' AS CLOB(1K)));
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
50                                                                                                                              
ij> INSERT INTO n VALUES (CAST('50' AS CLOB(1K)));
1 row inserted/updated/deleted
ij> VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K)));
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
50                                                                                                                              
ij> INSERT INTO n VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K)));
1 row inserted/updated/deleted
ij> VALUES cast('50' as long varchar);
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
50                                                                                                                              
ij> INSERT INTO n VALUES cast('50' as long varchar);
1 row inserted/updated/deleted
ij> -- test w LOBs
VALUES (CAST('50' AS BLOB(1K)));
ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
ij> INSERT INTO n VALUES (CAST('50' AS BLOB(1K)));
ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
ij> VALUES (CAST('50' AS CLOB(1K)));
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
50                                                                                                                              
ij> INSERT INTO n VALUES (CAST('50' AS CLOB(1K)));
1 row inserted/updated/deleted
ij> VALUES (CAST('50' AS NCLOB(1K)));
ERROR 0A000: Feature not implemented: NCLOB.
ij> INSERT INTO n VALUES (CAST('50' AS NCLOB(1K)));
ERROR 0A000: Feature not implemented: NCLOB.
ij> DROP TABLE n;
0 rows inserted/updated/deleted
ij>