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. -- ----- TEST TO RUN IN DB2 COMPATIBLITY MODE -- create table t1(c11 int); 0 rows inserted/updated/deleted ij> insert into t1 values(1); 1 row inserted/updated/deleted ij> -- equal tests are allowed only for BLOB==BLOB select c11 from t1 where cast(x'1111' as blob(5))=cast(x'1111' 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 c11 from t1 where cast(x'1111' as blob(5))=cast(x'1111' 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 c11 from t1 where cast(x'1110' as blob(5))=cast(x'1110' 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 c11 from t1 where cast(x'1111' as blob(5))=cast(x'11100000' 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 c11 from t1 where cast(x'1111' as blob(5))=cast(x'1110000000' 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 c11 from t1 where x'11' = cast(x'11' 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 c11 from t1 where cast(x'11' as blob(1)) = x'11'; 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 c11 from t1 where cast(x'11' as blob(1)) = cast(x'11' 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 c11 from t1 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 c11 from t1 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 c11 from t1 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 c11 from t1 where '1' = cast('1' as nclob(1)); ERROR 0A000: Feature not implemented: NCLOB. ij> select c11 from t1 where cast('1' as nclob(1)) = '1'; ERROR 0A000: Feature not implemented: NCLOB. ij> select c11 from t1 where cast('1' as nclob(1)) = cast('1' as nclob(1)); ERROR 0A000: Feature not implemented: NCLOB. ij> -- NCLOB is comparable with CLOB select c11 from t1 where cast('1' as nclob(10)) = cast('1' as clob(10)); ERROR 0A000: Feature not implemented: NCLOB. ij> select c11 from t1 where cast('1' as clob(10)) = cast('1' as nclob(10)); ERROR 0A000: Feature not implemented: NCLOB. ij> drop table b; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'B' because it does not exist. ij> drop table c; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'C' because it does not exist. ij> drop table n; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'N' because it does not exist. ij> create table b(blob blob(3K)); 0 rows inserted/updated/deleted ij> create table c(clob clob(2M)); 0 rows inserted/updated/deleted ij> create table n(nclob nclob(1G)); ERROR 0A000: Feature not implemented: NCLOB. ij> insert into b values(cast(X'0031' as blob(3K))); 1 row inserted/updated/deleted ij> insert into c values(cast('2' as clob(2M))); 1 row inserted/updated/deleted ij> insert into n values(cast('3' as nclob(1G))); ERROR 0A000: Feature not implemented: NCLOB. ij> insert into b values(cast(X'0031' as blob(3K))); 1 row inserted/updated/deleted ij> insert into c values(cast('2' as clob(2M))); 1 row inserted/updated/deleted ij> insert into n values(cast('3' as nclob(1G))); ERROR 0A000: Feature not implemented: NCLOB. ij> insert into b values(cast(X'0031' as blob(3K))); 1 row inserted/updated/deleted ij> insert into c values(cast('2' as clob(2M))); 1 row inserted/updated/deleted ij> insert into n values(cast('3' as nclob(1G))); ERROR 0A000: Feature not implemented: NCLOB. ij> select blob from b; BLOB -------------------------------------------------------------------------------------------------------------------------------- 0031 0031 0031 ij> select clob from c; CLOB -------------------------------------------------------------------------------------------------------------------------------- 2 2 2 ij> select nclob from n; ERROR 42X05: Table/View 'N' does not exist. 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'0001'; 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'0001'=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'0001'!=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 c.clob from c where c.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 n.nclob from n where n.nclob = '3'; ERROR 42X05: Table/View 'N' does not exist. ij> -- ORDER tests on LOB types (not allowed) select c11 from t1 where cast(x'1111' as blob(5))=cast(x'1111' 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 c11 from t1 where cast(x'1111' as blob(5))!=cast(x'1111' 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 c11 from t1 where cast(x'1111' as blob(5)) select c11 from t1 where cast(x'1111' as blob(5))>cast(x'1111' 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 c11 from t1 where cast(x'1111' as blob(5))<=cast(x'1110' 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 c11 from t1 where cast(x'1111' as blob(5))>=cast(x'11100000' 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 c11 from t1 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 c11 from t1 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 c11 from t1 where cast('fish' as clob(5)) select c11 from t1 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 c11 from t1 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 c11 from t1 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 c11 from t1 where cast('fish' as nclob(5))=cast('fish' as nclob(5)); ERROR 0A000: Feature not implemented: NCLOB. ij> select c11 from t1 where cast('fish' as nclob(5))!=cast('fish' as nclob(5)); ERROR 0A000: Feature not implemented: NCLOB. ij> select c11 from t1 where cast('fish' as nclob(5)) select c11 from t1 where cast('fish' as nclob(5))>cast('fish' as nclob(7)); ERROR 0A000: Feature not implemented: NCLOB. ij> select c11 from t1 where cast('fish' as nclob(5))<=cast('fish' as nclob(7)); ERROR 0A000: Feature not implemented: NCLOB. ij> select c11 from t1 where cast('fish' as nclob(5))>=cast('fish' as nclob(7)); ERROR 0A000: Feature not implemented: NCLOB. ij> -- BIT STRING literal is not allowed in DB2 values cast(B'1' as blob(10)); ERROR 42X01: Syntax error: Encountered "\'1\'" at line 2, column 14. ij>