java/testing/org/apache/derbyTesting/functionTests/master/bit.out - derby - Git at Google

 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 the builtin type 'bit'
 -- Specifically the base-16, hex bit literal

 -- stupid test of literals
 values(X'aAff');
 1
 ----
 aaff
 ij> -- casting to a for bit data type
 values (cast (x'ee' as char(2) for bit data));
 1
 ----
 ee20
 ij> values x'aAff' || (cast (x'ee' as char(2) for bit data));
 1
 --------
 aaffee20
 ij> -- are the search conditions true?
 create table tab1 (c1 char(25));
 0 rows inserted/updated/deleted
 ij> insert into tab1 values 'search condition is true';
 1 row inserted/updated/deleted
 ij> select * from tab1 where ((X'1010' || X'0011' || X'0100') = X'101000110100');
 C1
 -------------------------
 search condition is true
 ij> select * From tab1 where ((X'1010' || X'0011' || X'0100') = X'101000110100');
 C1
 -------------------------
 search condition is true
 ij> select * from tab1 where (X'1100' > X'0011');
 C1
 -------------------------
 search condition is true
 ij> drop table tab1;
 0 rows inserted/updated/deleted
 ij> -- simple negative test
 values(X'gg');
 ERROR 42X01: Syntax error: Encountered "\'gg\'" at line 2, column 9.
 Issue the 'help' command for general information on IJ command syntax.
 Any unrecognized commands are treated as potential SQL commands and executed directly.
 Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
 ij> values(X'z');
 ERROR 42X01: Syntax error: Encountered "\'z\'" at line 1, column 9.
 Issue the 'help' command for general information on IJ command syntax.
 Any unrecognized commands are treated as potential SQL commands and executed directly.
 Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
 ij> values(X'zz');
 ERROR 42X01: Syntax error: Encountered "\'zz\'" at line 1, column 9.
 Issue the 'help' command for general information on IJ command syntax.
 Any unrecognized commands are treated as potential SQL commands and executed directly.
 Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
 ij> -- fails after bug 5742 is fixed
 values(X'9');
 ERROR 42606: An invalid hexadecimal constant starting with 'X'9'' has been detected.
 ij> -- some quick tests of the length function
 -- # bits in a string expression
 values({fn length(X'ab')} * 8);
 ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'VARCHAR'.
 ij> values({fn length(X'11')} * 8);
 ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'VARCHAR'.
 ij> -- # characters in a string expression
 values({fn length(X'ab')});
 ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'VARCHAR'.
 ij> values({fn length(X'11')});
 ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'VARCHAR'.
 ij> -- # octets in a string expression
 values({fn length(X'ab')});
 ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'VARCHAR'.
 ij> values({fn length(X'11')});
 ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'VARCHAR'.
 ij> -- stupid test for syntax
 create table t1 (b1 char for bit data, b2 char(2) for bit data, b3 varchar(2) for bit data, b4 LONG VARCHAR FOR BIT DATA,
 				b5 LONG VARCHAR FOR BIT DATA, b6 LONG VARCHAR FOR BIT DATA);
 0 rows inserted/updated/deleted
 ij> drop table t1;
 0 rows inserted/updated/deleted
 ij> create table t1 (b1 char for bit data, b2 char(1) for bit data not null, b3 varchar(1) for bit data not null,
 				b4 LONG VARCHAR FOR BIT DATA not null, b5 LONG VARCHAR FOR BIT DATA not null,
 				b6 LONG VARCHAR FOR BIT DATA not null);
 0 rows inserted/updated/deleted
 ij> drop table t1;
 0 rows inserted/updated/deleted
 ij> create table t (i int, s smallint, c char(10), v varchar(50),
 	d double precision, r real, b char (2) for bit data, bv varchar(8) for bit data,
 	lbv LONG VARCHAR FOR BIT DATA);
 0 rows inserted/updated/deleted
 ij> -- explicit null
 insert into t values (null, null, null, null, null, null, null, null, null);
 1 row inserted/updated/deleted
 ij> -- implicit null
 insert into t (i) values (null);
 1 row inserted/updated/deleted
 ij> select b, bv, lbv from t;
 B   |BV              |LBV
 ------------------------------------------------------------------------------------------------------------------------------------------------------
 NULL|NULL            |NULL
 NULL|NULL            |NULL
 ij> -- sample data
 insert into t values (0, 100, 'hello', 'everyone is here', 200.0e0, 200.0e0,
 			X'12af', X'0000111100001111', X'abc123');
 1 row inserted/updated/deleted
 ij> insert into t values (-1, -100, 'goodbye', 'everyone is there', -200.0e0, -200.0e0,
 			X'0000', X'', X'10101010');
 1 row inserted/updated/deleted
 ij> -- truncation -- should get an error
 insert into t (b, bv) values (X'ffffffff', X'ffffffff');
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA 'ffffffff' to length 2.
 ij> select b, bv, lbv from t;
 B   |BV              |LBV
 ------------------------------------------------------------------------------------------------------------------------------------------------------
 NULL|NULL            |NULL
 NULL|NULL            |NULL
 12af|0000111100001111|abc123
 0000|                |10101010
 ij> -- padding -- will be warning, some day (not now)
 insert into t (b, bv) values (X'01', X'01');
 1 row inserted/updated/deleted
 ij> insert into t (b, bv) values (X'', X'');
 1 row inserted/updated/deleted
 ij> select b, bv from t;
 B   |BV
 ---------------------
 NULL|NULL
 NULL|NULL
 12af|0000111100001111
 0000|
 0120|01
 2020|
 ij> drop table t;
 0 rows inserted/updated/deleted
 ij> --
 -- simple comparisons
 -- returns 1 if the search conditions are true
 --
 create table nulltab (b char(1) for bit data);
 0 rows inserted/updated/deleted
 ij> insert into nulltab values (null);
 1 row inserted/updated/deleted
 ij> select 1 from nulltab where X'0001' > X'0000';
 1
 -----------
 1
 ij> select 1 from nulltab where X'0100' > X'0001';
 1
 -----------
 1
 ij> select 1 from nulltab where X'ff00' > X'00ff';
 1
 -----------
 1
 ij> select 1 from nulltab where X'0100' > X'0100';
 1
 -----------
 ij> select 1 from nulltab where X'0100' > b;
 1
 -----------
 ij> select 1 from nulltab where X'0001' >= X'0000';
 1
 -----------
 1
 ij> select 1 from nulltab where X'0100' >= X'0001';
 1
 -----------
 1
 ij> select 1 from nulltab where X'ff00' >= X'00ff';
 1
 -----------
 1
 ij> select 1 from nulltab where X'0100' >= b;
 1
 -----------
 ij> select 1 from nulltab where X'0001' < X'0000';
 1
 -----------
 ij> select 1 from nulltab where X'0100' < X'0001';
 1
 -----------
 ij> select 1 from nulltab where X'ff00' < X'00ff';
 1
 -----------
 ij> select 1 from nulltab where X'0100' < b;
 1
 -----------
 ij> select 1 from nulltab where X'0001' <= X'0000';
 1
 -----------
 ij> select 1 from nulltab where X'0100' <= X'0001';
 1
 -----------
 ij> select 1 from nulltab where X'ff00' <= X'00ff';
 1
 -----------
 ij> select 1 from nulltab where X'0100' <= b;
 1
 -----------
 ij> drop table nulltab;
 0 rows inserted/updated/deleted
 ij> --
 -- select comparisons
 --

 create table t (b10 char(20) for bit data, vb10 varchar(20) for bit data, b16 char(2) for bit data, vb16 varchar(2) for bit data, lbv LONG VARCHAR FOR BIT DATA, c20 char(20), cv20 varchar(20));
 0 rows inserted/updated/deleted
 ij> insert into t values (null, null, null, null, null, 'null', 'null columns');
 1 row inserted/updated/deleted
 ij> insert into t values (X'',  X'',  X'',  X'', X'', '0', 'zero length column');
 1 row inserted/updated/deleted
 ij> insert into t values (X'0000000001', X'0000000001', X'01', X'01', X'0000000001', '1', '1');
 1 row inserted/updated/deleted
 ij> insert into t values (X'0000000011', X'0000000011', X'03', X'03', X'03', '3', '3');
 1 row inserted/updated/deleted
 ij> insert into t values (X'1111111111', X'1111111111', X'ff', X'ff', X'1111111111', 'ff', 'ff');
 1 row inserted/updated/deleted
 ij> insert into t values (X'11', X'11', X'aa', X'aa', X'aa', 'aa', 'aa');
 1 row inserted/updated/deleted
 ij> -- make sure built-in functions work ok on binary types,
 -- it is a little special since it maps to an
 -- array.  use length to make sure it wont
 -- diff from run to run
 select {fn length(cast(b10 as char(10)))} from t where b10 is not null;
 ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'CHAR'.
 ij> select {fn length(cast(vb10 as char(10)))} from t where vb10 is not null;
 ERROR 42846: Cannot convert types 'VARCHAR () FOR BIT DATA' to 'CHAR'.
 ij> select {fn length(cast(lbv as char(10)))} from t where vb10 is not null;
 ERROR 42846: Cannot convert types 'LONG VARCHAR FOR BIT DATA' to 'CHAR'.
 ij> select b10, c20, cv20 from t order by b10 asc;
 B10                                     |C20                 |CV20
 ----------------------------------------------------------------------------------
 0000000001202020202020202020202020202020|1                   |1
 0000000011202020202020202020202020202020|3                   |3
 1111111111202020202020202020202020202020|ff                  |ff
 1120202020202020202020202020202020202020|aa                  |aa
 2020202020202020202020202020202020202020|0                   |zero length column
 NULL                                    |null                |null columns
 ij> select b10, c20, cv20 from t order by b10 desc;
 B10                                     |C20                 |CV20
 ----------------------------------------------------------------------------------
 NULL                                    |null                |null columns
 2020202020202020202020202020202020202020|0                   |zero length column
 1120202020202020202020202020202020202020|aa                  |aa
 1111111111202020202020202020202020202020|ff                  |ff
 0000000011202020202020202020202020202020|3                   |3
 0000000001202020202020202020202020202020|1                   |1
 ij> select vb10, c20, cv20 from t order by vb10;
 VB10                                    |C20                 |CV20
 ----------------------------------------------------------------------------------
                                         |0                   |zero length column
 0000000001                              |1                   |1
 0000000011                              |3                   |3
 11                                      |aa                  |aa
 1111111111                              |ff                  |ff
 NULL                                    |null                |null columns
 ij> select b16, c20, cv20 from t order by b16;
 B16 |C20                 |CV20
 ----------------------------------------------
 0120|1                   |1
 0320|3                   |3
 2020|0                   |zero length column
 aa20|aa                  |aa
 ff20|ff                  |ff
 NULL|null                |null columns
 ij> select vb16, c20, cv20 from t order by vb16;
 VB16|C20                 |CV20
 ----------------------------------------------
     |0                   |zero length column
 01  |1                   |1
 03  |3                   |3
 aa  |aa                  |aa
 ff  |ff                  |ff
 NULL|null                |null columns
 ij> select vb16, c20, cv20, lbv from t order by lbv;
 ERROR X0X67: Columns of type 'LONG VARCHAR FOR BIT DATA' 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> select b10 from t where b10 > X'0000000010';
 B10
 ----------------------------------------
 2020202020202020202020202020202020202020
 0000000011202020202020202020202020202020
 1111111111202020202020202020202020202020
 1120202020202020202020202020202020202020
 ij> select b10 from t where b10 < X'0000000010';
 B10
 ----------------------------------------
 0000000001202020202020202020202020202020
 ij> select b10 from t where b10 <= X'0000000011';
 B10
 ----------------------------------------
 0000000001202020202020202020202020202020
 0000000011202020202020202020202020202020
 ij> select b10 from t where b10 >= X'0000000011';
 B10
 ----------------------------------------
 2020202020202020202020202020202020202020
 0000000011202020202020202020202020202020
 1111111111202020202020202020202020202020
 1120202020202020202020202020202020202020
 ij> select b10 from t where b10 <> X'0000000011';
 B10
 ----------------------------------------
 2020202020202020202020202020202020202020
 0000000001202020202020202020202020202020
 1111111111202020202020202020202020202020
 1120202020202020202020202020202020202020
 ij> select vb10 from t where vb10 > X'0000000010';
 VB10
 ----------------------------------------
 0000000011
 1111111111
 11
 ij> select vb10 from t where vb10 < X'0000000010';
 VB10
 ----------------------------------------

 0000000001
 ij> select vb10 from t where vb10 <= X'0000000011';
 VB10
 ----------------------------------------

 0000000001
 0000000011
 ij> select vb10 from t where vb10 >= X'0000000011';
 VB10
 ----------------------------------------
 0000000011
 1111111111
 11
 ij> select vb10 from t where vb10 <> X'0000000011';
 VB10
 ----------------------------------------

 0000000001
 1111111111
 11
 ij> select b16 from t where b16 > X'0000000010';
 B16
 ----
 2020
 0120
 0320
 ff20
 aa20
 ij> select b16 from t where b16 < X'0000000010';
 B16
 ----
 ij> select b16 from t where b16 <= X'0000000011';
 B16
 ----
 ij> select b16 from t where b16 >= X'0000000011';
 B16
 ----
 2020
 0120
 0320
 ff20
 aa20
 ij> select b16 from t where b16 <> X'0000000011';
 B16
 ----
 2020
 0120
 0320
 ff20
 aa20
 ij> select vb16 from t where vb16 > X'0000000010';
 VB16
 ----
 01
 03
 ff
 aa
 ij> select vb16 from t where vb16 < X'0000000010';
 VB16
 ----

 ij> select vb16 from t where vb16 <= X'0000000011';
 VB16
 ----

 ij> select vb16 from t where vb16 >= X'0000000011';
 VB16
 ----
 01
 03
 ff
 aa
 ij> select vb16 from t where vb16 <> X'0000000011';
 VB16
 ----

 01
 03
 ff
 aa
 ij> select lbv from t where lbv > X'0000000010';
 ERROR 42818: Comparisons between 'LONG VARCHAR FOR BIT DATA' 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 lbv from t where lbv < X'0000000010';
 ERROR 42818: Comparisons between 'LONG VARCHAR FOR BIT DATA' 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 lbv from t where lbv <= X'0000000011';
 ERROR 42818: Comparisons between 'LONG VARCHAR FOR BIT DATA' 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 lbv from t where lbv >= X'0000000011';
 ERROR 42818: Comparisons between 'LONG VARCHAR FOR BIT DATA' 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 lbv from t where lbv <> X'0000000011';
 ERROR 42818: Comparisons between 'LONG VARCHAR FOR BIT DATA' 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 b10, vb10||X'11' from t where vb10||X'11' > b10;
 B10                                     |2
 -----------------------------------------------------------------------------------
 ij> select b10, X'11'||vb10 from t where X'11'||vb10 > b10;
 B10                                     |2
 -----------------------------------------------------------------------------------
 0000000001202020202020202020202020202020|110000000001
 0000000011202020202020202020202020202020|110000000011
 ij> select b16, vb16||X'11' from t where vb16||X'11' > b16;
 B16 |2
 -----------
 ij> select b10 || vb10 from t;
 1
 --------------------------------------------------------------------------------
 NULL
 2020202020202020202020202020202020202020
 00000000012020202020202020202020202020200000000001
 00000000112020202020202020202020202020200000000011
 11111111112020202020202020202020202020201111111111
 112020202020202020202020202020202020202011
 ij> select lbv || b10 from t;
 1
 --------------------------------------------------------------------------------------------------------------------------------
 NULL
 2020202020202020202020202020202020202020
 00000000010000000001202020202020202020202020202020
 030000000011202020202020202020202020202020
 11111111111111111111202020202020202020202020202020
 aa1120202020202020202020202020202020202020
 ij> select b10 || lbv from t;
 1
 --------------------------------------------------------------------------------------------------------------------------------
 NULL
 2020202020202020202020202020202020202020
 00000000012020202020202020202020202020200000000001
 000000001120202020202020202020202020202003
 11111111112020202020202020202020202020201111111111
 1120202020202020202020202020202020202020aa
 ij> select lbv || vb10 from t;
 1
 --------------------------------------------------------------------------------------------------------------------------------
 NULL

 00000000010000000001
 030000000011
 11111111111111111111
 aa11
 ij> select vb10 || lbv from t;
 1
 --------------------------------------------------------------------------------------------------------------------------------
 NULL

 00000000010000000001
 000000001103
 11111111111111111111
 11aa
 ij> select t1.b10 from t t1, t t2 where t1.b10 > t2.b10;
 B10
 ----------------------------------------
 2020202020202020202020202020202020202020
 2020202020202020202020202020202020202020
 2020202020202020202020202020202020202020
 2020202020202020202020202020202020202020
 0000000011202020202020202020202020202020
 1111111111202020202020202020202020202020
 1111111111202020202020202020202020202020
 1120202020202020202020202020202020202020
 1120202020202020202020202020202020202020
 1120202020202020202020202020202020202020
 ij> -- FUNCTIONS
 -- some length functions
 select {fn length(b10)} from t;
 ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'VARCHAR'.
 ij> select {fn length(vb10)} from t;
 ERROR 42846: Cannot convert types 'VARCHAR () FOR BIT DATA' to 'VARCHAR'.
 ij> select {fn length(lbv)} from t;
 ERROR 42846: Cannot convert types 'LONG VARCHAR FOR BIT DATA' to 'VARCHAR'.
 ij> select {fn length(c20)} from t;
 1
 -----------
 4
 1
 1
 1
 2
 2
 ij> select {fn length(cv20)} from t;
 1
 -----------
 12
 18
 1
 1
 2
 2
 ij> drop table t;
 0 rows inserted/updated/deleted
 ij> -----------------------
 -- test normalization
 -----------------------
 create table t1 (c1 char(2) for bit data);
 0 rows inserted/updated/deleted
 ij> insert into t1 values (X'0001');
 1 row inserted/updated/deleted
 ij> insert into t1 values (X'0010');
 1 row inserted/updated/deleted
 ij> insert into t1 values (X'0011');
 1 row inserted/updated/deleted
 ij> select * from t1;
 C1
 ----
 0001
 0010
 0011
 ij> -- now insert something that needs to be expanded
 insert into t1 values (X'11');
 1 row inserted/updated/deleted
 ij> select * from t1;
 C1
 ----
 0001
 0010
 0011
 1120
 ij> -- insert select, expand 1 byte
 create table t2 (c1 char(3) for bit data);
 0 rows inserted/updated/deleted
 ij> insert into t2 select c1 from t1;
 4 rows inserted/updated/deleted
 ij> select * from t2;
 C1
 ------
 000120
 001020
 001120
 112020
 ij> drop table t2;
 0 rows inserted/updated/deleted
 ij> -- insert select, expand many bytes
 create table t2 (c1 char(20) for bit data);
 0 rows inserted/updated/deleted
 ij> insert into t2 select c1 from t1;
 4 rows inserted/updated/deleted
 ij> select * from t2;
 C1
 ----------------------------------------
 0001202020202020202020202020202020202020
 0010202020202020202020202020202020202020
 0011202020202020202020202020202020202020
 1120202020202020202020202020202020202020
 ij> drop table t2;
 0 rows inserted/updated/deleted
 ij> drop table t1;
 0 rows inserted/updated/deleted
 ij> --
 -- some extra tests for truncation.  in 2.0
 create table t1 (b1 char(1) for bit data);
 0 rows inserted/updated/deleted
 ij> -- ok
 insert into t1 values (X'11');
 1 row inserted/updated/deleted
 ij> -- valid length
 insert into t1 values (X'10');
 1 row inserted/updated/deleted
 ij> insert into t1 values (X'11');
 1 row inserted/updated/deleted
 ij> -- truncation errors
 insert into t1 values (X'1000');
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '1000' to length 1.
 ij> insert into t1 values (X'100000');
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '100000' to length 1.
 ij> insert into t1 values (X'10000000');
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '10000000' to length 1.
 ij> insert into t1 values (X'1000000000');
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '1000000000' to length 1.
 ij> insert into t1 values (X'100001');
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '100001' to length 1.
 ij> insert into t1 values (X'0001');
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '0001' to length 1.
 ij> insert into t1 values (X'8001');
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '8001' to length 1.
 ij> insert into t1 values (X'8000');
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '8000' to length 1.
 ij> drop table t1;
 0 rows inserted/updated/deleted
 ij> create table t1 (b9 char(2) for bit data);
 0 rows inserted/updated/deleted
 ij> -- ok
 insert into t1 values (X'1111');
 1 row inserted/updated/deleted
 ij> -- truncation errors
 insert into t1 values (X'111100');
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '111100' to length 2.
 ij> insert into t1 values (X'11110000');
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '11110000' to length 2.
 ij> insert into t1 values (X'1111000000');
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '1111000000' to length 2.
 ij> insert into t1 values (X'1111111100000000');
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '1111111100000000' to length 2.
 ij> insert into t1 values (X'1111111111');
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '1111111111' to length 2.
 ij> insert into t1 values (X'11111111100001');
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '11111111100001' to length 2.
 ij> insert into t1 values (X'0001');
 1 row inserted/updated/deleted
 ij> insert into t1 values (X'8001');
 1 row inserted/updated/deleted
 ij> insert into t1 values (X'8000');
 1 row inserted/updated/deleted
 ij> drop table t1;
 0 rows inserted/updated/deleted
 ij> -- a few other conditions
 create table t1 (b3 char(2) for bit data, b7 char(4) for bit data, b8 char (5) for bit data, b15 char(8) for bit data, b16 char(9) for bit data);
 0 rows inserted/updated/deleted
 ij> -- ok
 insert into t1 values
 (
 		X'1111',
 		X'11111111',
 		X'1111111111',
 		X'1111111111111111',
 		X'111111111111111111'
 );
 1 row inserted/updated/deleted
 ij> -- ok
 insert into t1 values
 (
 		X'1110',
 		X'11111110',
 		X'11111111',
 		X'1111111111111110',
 		X'1111111111111111'
 );
 1 row inserted/updated/deleted
 ij> -- bad
 -- truncation error for column b8
 insert into t1 values
 (
 		null,
 		null,
 		X'111111111110',
 		null,
 		null
 );
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '111111111110' to length 5.
 ij> -- truncation error for column b7
 insert into t1 values
 (
 		null,
 		X'1111111100',
 		null,
 		null,
 		null
 );
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '1111111100' to length 4.
 ij> -- truncation error for column b7
 insert into t1 values
 (
 		null,
 		X'1111111111',
 		null,
 		null,
 		null
 );
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '1111111111' to length 4.
 ij> -- truncation error for column b15
 insert into t1 values
 (
 		null,
 		null,
 		null,
 		X'111111111111111100',
 		null
 );
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '111111111111111100' to length 8.
 ij> -- truncation error for column b15
 insert into t1 values
 (
 		null,
 		null,
 		null,
 		X'111111111111111111',
 		null
 );
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '111111111111111111' to length 8.
 ij> -- truncation error for column b16
 insert into t1 values
 (
 		null,
 		null,
 		null,
 		null,
 		X'11111111111111111110'
 );
 ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '11111111111111111110' to length 9.
 ij> AUTOCOMMIT OFF;
 ij> -- bug 5160 - incorrect typing of VALUES table constructor on an insert;

 create table iv (id int, vc varchar(12));
 0 rows inserted/updated/deleted
 ij> insert into iv values (1, 'abc'), (2, 'defghijk'), (3, 'lmnopqrstcc');
 3 rows inserted/updated/deleted
 ij> insert into iv values (4, null), (5, 'null ok?'), (6, '2blanks  ');
 3 rows inserted/updated/deleted
 ij> insert into iv values (7, 'dddd'), (8, '0123456789123'), (9, 'too long');
 ERROR 22001: A truncation error was encountered trying to shrink VARCHAR '0123456789123' to length 12.
 ij> select id, vc, {fn length(vc)} AS LEN from iv order by 1;
 ID         |VC          |LEN
 ------------------------------------
 1          |abc         |3
 2          |defghijk    |8
 3          |lmnopqrstcc |11
 4          |NULL        |NULL
 5          |null ok?    |8
 6          |2blanks     |7
 ij> -- the inner values must not be changed to VARCHAR as it is not the table constructor
 insert into iv select * from (values (10, 'pad'), (11, 'pad me'), (12, 'anakin jedi')) as t(i, c);
 3 rows inserted/updated/deleted
 ij> select id, vc, {fn length(vc)} AS LEN from iv order by 1;
 ID         |VC          |LEN
 ------------------------------------
 1          |abc         |3
 2          |defghijk    |8
 3          |lmnopqrstcc |11
 4          |NULL        |NULL
 5          |null ok?    |8
 6          |2blanks     |7
 10         |pad         |3
 11         |pad me      |6
 12         |anakin jedi |11
 ij> -- check values outside of table constructors retain their CHARness
 select c, {fn length(c)} AS LEN from (values (1, 'abc'), (2, 'defghijk'), (3, 'lmnopqrstcc')) as t(i, c);
 C          |LEN
 -----------------------
 abc        |3
 defghijk   |8
 lmnopqrstcc|11
 ij> drop table iv;
 0 rows inserted/updated/deleted
 ij> create table bv (id int, vb varchar(16) for bit data);
 0 rows inserted/updated/deleted
 ij> insert into bv values (1, X'1a'), (2, X'cafebabe'), (3, null);
 3 rows inserted/updated/deleted
 ij> select id, vb, {fn length(vb)} AS LEN from bv order by 1;
 ERROR 42846: Cannot convert types 'VARCHAR () FOR BIT DATA' to 'VARCHAR'.
 ij> drop table bv;
 0 rows inserted/updated/deleted
 ij> create table dv (id int, vc varchar(12));
 0 rows inserted/updated/deleted
 ij> -- beetle 5568
 -- should fail because DB2 doesn't allow this implicit casting to string
 insert into dv values (1, 1.2), (2, 34.5639), (3, null);
 ERROR 42X61: Types 'DECIMAL' and 'VARCHAR' are not UNION compatible.
 ij> -- should pass
 insert into dv values (1, '1.2'), (2, '34.5639'), (3, null);
 3 rows inserted/updated/deleted
 ij> select id, vc from dv order by 1;
 ID         |VC
 ------------------------
 1          |1.2
 2          |34.5639
 3          |NULL
 ij> drop table dv;
 0 rows inserted/updated/deleted
 ij> -- bug 5306 -- incorrect padding of VALUES table constructor on an insert,
 -- when implicit casting (bit->char or char->bit) is used.

 -- 5306: Char -> For Bit Data Types

 create table bitTable (id int, bv LONG VARCHAR FOR BIT DATA);
 0 rows inserted/updated/deleted
 ij> insert into bitTable values (1, X'031'), (2, X'032'), (3, X'');
 ERROR 42606: An invalid hexadecimal constant starting with 'X'031'' has been detected.
 ij> insert into bitTable values (4, null), (5, X'033'), (6, X'2020');
 ERROR 42606: An invalid hexadecimal constant starting with 'X'033'' has been detected.
 ij> select id, bv, {fn length(bv)} as LEN from bitTable order by 1;
 ERROR 42846: Cannot convert types 'LONG VARCHAR FOR BIT DATA' to 'VARCHAR'.
 ij> -- the inner values must not be changed to varying, as it is not the table constructor
 insert into bitTable select * from (values (10, 'pad'), (11, 'pad me'), (12, 'anakin jedi')) as t(i, c);
 ERROR 42821: Columns of type 'LONG VARCHAR FOR BIT DATA' cannot hold values of type 'CHAR'.
 ij> select id, bv, {fn length(bv)} AS LEN from bitTable order by 1;
 ERROR 42846: Cannot convert types 'LONG VARCHAR FOR BIT DATA' to 'VARCHAR'.
 ij> drop table bitTable;
 0 rows inserted/updated/deleted
 ij> -- 5306: Bit -> Char

 create table charTable (id int, cv long varchar);
 0 rows inserted/updated/deleted
 ij> insert into charTable values (1, x'0101'), (2, x'00101100101001'), (3, x'');
 ERROR 42821: Columns of type 'LONG VARCHAR' cannot hold values of type 'LONG VARCHAR FOR BIT DATA'.
 ij> insert into charTable values (4, null), (5, x'1010101111'), (6, x'1000');
 ERROR 42X61: Types 'LONG VARCHAR' and 'LONG VARCHAR FOR BIT DATA' are not UNION compatible.
 ij> select id, cv, {fn length(cv)} as LEN from charTable order by 1;
 ID         |CV                                                                                                                              |LEN
 --------------------------------------------------------------------------------------------------------------------------------------------------------
 ij> -- the inner values must not be changed to varying, as it is not the table constructor
 insert into charTable select * from (values (10, x'001010'), (11, x'01011010101111'), (12, x'0101010101000010100101110101')) as t(i, c);
 ERROR 42821: Columns of type 'LONG VARCHAR' cannot hold values of type 'CHAR () FOR BIT DATA'.
 ij> select id, cv, {fn length(cv)} AS LEN from charTable order by 1;
 ID         |CV                                                                                                                              |LEN
 --------------------------------------------------------------------------------------------------------------------------------------------------------
 ij> drop table charTable;
 0 rows inserted/updated/deleted
 ij> -- Verify that 5306 still works with Union.

 create table pt5 (b5 char(2) for bit data);
 0 rows inserted/updated/deleted
 ij> create table pt10 (b10 char (4) for bit data);
 0 rows inserted/updated/deleted
 ij> insert into pt10 values (x'01000110');
 1 row inserted/updated/deleted
 ij> insert into pt5 values (x'1010');
 1 row inserted/updated/deleted
 ij> select {fn length(CM)} from (select b5 from pt5 union all select b10 from pt10) as t(CM);
 ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'VARCHAR'.
 ij> drop table pt5;
 0 rows inserted/updated/deleted
 ij> drop table pt10;
 0 rows inserted/updated/deleted
 ij> -- beetle 5612

 create table t5612 (c1 char(10), c2 varchar(10), c3 long  varchar);
 0 rows inserted/updated/deleted
 ij> insert into t5612 values (X'00680069', X'00680069', X'00680069');
 ERROR 42821: Columns of type 'CHAR' cannot hold values of type 'CHAR () FOR BIT DATA'.
 ij> select * from t5612;
 C1        |C2        |C3
 ------------------------------------------------------------------------------------------------------------------------------------------------------
 ij> values cast(X'00680069' as char(30)), cast(X'00680069' as varchar(30)), cast(X'00680069' as long varchar);
 ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'CHAR'.
 ij> -- DERBY-1085

 create table npetest1 (col1 varchar(36) for bit data not null, constraint pknpe1 primary key (col1));
 0 rows inserted/updated/deleted
 ij> create table npetest2 (col2 varchar(36) for bit data, constraint fknpe1 foreign key (col2) references npetest1(col1) on delete cascade);
 0 rows inserted/updated/deleted
 ij> insert into npetest1 (col1) values (X'0000000001');
 1 row inserted/updated/deleted
 ij> insert into npetest1 (col1) values (X'0000000002');
 1 row inserted/updated/deleted
 ij> insert into npetest1 (col1) values (X'0000000003');
 1 row inserted/updated/deleted
 ij> insert into npetest2 (col2) values (X'0000000001');
 1 row inserted/updated/deleted
 ij> insert into npetest2 (col2) values (NULL);
 1 row inserted/updated/deleted
 ij> insert into npetest2 (col2) values (X'0000000002');
 1 row inserted/updated/deleted
 ij> select col1 from npetest1 where col1 not in (select col2 from npetest2);
 COL1
 ------------------------------------------------------------------------
 ij> select col1 from npetest1 where col1 not in (select col2 from npetest2 where col2 is not null);
 COL1
 ------------------------------------------------------------------------
 0000000003
 ij> drop table npetest2;
 0 rows inserted/updated/deleted
 ij> drop table npetest1;
 0 rows inserted/updated/deleted
 ij>