lob.out

derby database source code.good for you.

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.
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.
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.
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.
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.
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.
ij> select 1 from b where cast('fish' as clob(5))=cast('fish' as clob(5));
ERROR 42818: Comparisons between 'CLOB' and 'CLOB' are not supported.
ij> select 1 from b where cast('fish' as clob(5))!=cast('fish' as clob(5));
ERROR 42818: Comparisons between 'CLOB' and 'CLOB' are not supported.
ij> select 1 from b where cast('fish' as clob(5))<cast('fish' as clob(5));
ERROR 42818: Comparisons between 'CLOB' and 'CLOB' are not supported.
ij> select 1 from b where cast('fish' as clob(5))>cast('fish' as clob(7));
ERROR 42818: Comparisons between 'CLOB' and 'CLOB' are not supported.
ij> select 1 from b where cast('fish' as clob(5))<=cast('fish' as clob(7));
ERROR 42818: Comparisons between 'CLOB' and 'CLOB' are not supported.
ij> select 1 from b where cast('fish' as clob(5))>=cast('fish' as clob(7));
ERROR 42818: Comparisons between 'CLOB' and 'CLOB' are not supported.
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' and 'INTEGER' are not supported.
ij> select * from testoperatorclob where colone > 5;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorclob where colone < 70;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorclob where colone = 50;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorclob where colone != 10;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorclob where colone <= 70;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorclob where colone >= 10;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorclob where colone <> 10;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorclob where colone > '10';
ERROR 42818: Comparisons between 'CLOB' and 'CHAR' are not supported.
ij> select * from testoperatorclob where colone > '5';
ERROR 42818: Comparisons between 'CLOB' and 'CHAR' are not supported.
ij> select * from testoperatorclob where colone < '70';
ERROR 42818: Comparisons between 'CLOB' and 'CHAR' are not supported.
ij> select * from testoperatorclob where colone = '50';
ERROR 42818: Comparisons between 'CLOB' and 'CHAR' are not supported.
ij> select * from testoperatorclob where colone != '10';
ERROR 42818: Comparisons between 'CLOB' and 'CHAR' are not supported.
ij> select * from testoperatorclob where colone <= '70';
ERROR 42818: Comparisons between 'CLOB' and 'CHAR' are not supported.
ij> select * from testoperatorclob where colone >= '10';
ERROR 42818: Comparisons between 'CLOB' and 'CHAR' are not supported.
ij> select * from testoperatorclob where colone <> '10';
ERROR 42818: Comparisons between 'CLOB' and 'CHAR' are not supported.
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.
ij> select * from testoperatorblob where colone > 5;
ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorblob where colone < 999999;
ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorblob where colone = 00350030;
ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorblob where colone != 10;
ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorblob where colone <= 999999;
ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorblob where colone >= 10;
ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorblob where colone <> 10;
ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorblob where colone > '10';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR' are not supported.
ij> select * from testoperatorblob where colone > '5';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR' are not supported.
ij> select * from testoperatorblob where colone < '70';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR' are not supported.
ij> select * from testoperatorblob where colone = '50';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR' are not supported.
ij> select * from testoperatorblob where colone != '10';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR' are not supported.
ij> select * from testoperatorblob where colone <= '70';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR' are not supported.
ij> select * from testoperatorblob where colone >= '10';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR' are not supported.
ij> select * from testoperatorblob where colone <> '10';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR' are not supported.
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 '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 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone > 5;
ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone < 70;
ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone = 50;
ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone != 10;
ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone <= 70;
ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone >= 10;
ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone <> 10;
ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone > '10';
ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone > '5';
ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone < '70';
ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone = '50';
ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone != '10';
ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone <= '70';
ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone >= '10';
ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist.
ij> select * from testoperatornclob where colone <> '10';
ERROR 42X05: Table '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> -- LOB column as parameter not allowed
select b->equals('3') from b;
ERROR XJ082: BLOB/CLOB values are not allowed as method parameters or receiver.
ij> select c->equals('3') from c;
ERROR XJ082: BLOB/CLOB values are not allowed as method parameters or receiver.
ij> -- explicit LOB as parameter not allowed
values '3'->equals(cast('3' as blob(7)));
ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
ij> values '3'->equals(cast('3' as clob(7)));
ERROR XJ082: BLOB/CLOB values are not allowed as method parameters or receiver.
ij> -- LOB column as parameter not allowed
select '3'->equals(b) from b;
ERROR XJ082: BLOB/CLOB values are not allowed as method parameters or receiver.
ij> select '3'->equals(c) from c;
ERROR XJ082: BLOB/CLOB values are not allowed as method parameters or receiver.
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' and 'LONG VARCHAR' are not supported.
ij> -- NOT IN predicate
select c1 from testPredicate1 where c1 NOT IN (select c1 from testPredicate2);
ERROR 42818: Comparisons between 'LONG VARCHAR' and 'LONG VARCHAR' are not supported.
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' and 'LONG VARCHAR' are not supported.
ij> select * from testPredicate1 LEFT OUTER JOIN testPredicate2 on testPredicate1.c1=testPredicate2.c1;
ERROR 42818: Comparisons between 'LONG VARCHAR' and 'LONG VARCHAR' are not supported.
ij> -- PRIMARY KEY