| 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> |
