📄 bit.out
字号:
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/deletedij> create table t1 (b9 char(2) for bit data);0 rows inserted/updated/deletedij> -- okinsert into t1 values (X'1111');1 row inserted/updated/deletedij> -- truncation errorsinsert 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/deletedij> insert into t1 values (X'8001');1 row inserted/updated/deletedij> insert into t1 values (X'8000');1 row inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> -- a few other conditionscreate 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/deletedij> -- okinsert into t1 values( X'1111', X'11111111', X'1111111111', X'1111111111111111', X'111111111111111111');1 row inserted/updated/deletedij> -- okinsert into t1 values( X'1110', X'11111110', X'11111111', X'1111111111111110', X'1111111111111111');1 row inserted/updated/deletedij> -- bad-- truncation error for column b8insert 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 b7insert 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 b7insert 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 b15insert 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 b15insert 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 b16insert 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/deletedij> insert into iv values (1, 'abc'), (2, 'defghijk'), (3, 'lmnopqrstcc');3 rows inserted/updated/deletedij> insert into iv values (4, null), (5, 'null ok?'), (6, '2blanks ');3 rows inserted/updated/deletedij> 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 constructorinsert into iv select * from (values (10, 'pad'), (11, 'pad me'), (12, 'anakin jedi')) as t(i, c);3 rows inserted/updated/deletedij> 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 CHARnessselect 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/deletedij> create table bv (id int, vb varchar(16) for bit data);0 rows inserted/updated/deletedij> insert into bv values (1, X'1a'), (2, X'cafebabe'), (3, null);3 rows inserted/updated/deletedij> 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/deletedij> create table dv (id int, vc varchar(12));0 rows inserted/updated/deletedij> -- beetle 5568-- should fail because DB2 doesn't allow this implicit casting to stringinsert into dv values (1, 1.2), (2, 34.5639), (3, null);ERROR 42X61: Types 'DECIMAL' and 'VARCHAR' are not UNION compatible.ij> -- should passinsert into dv values (1, '1.2'), (2, '34.5639'), (3, null);3 rows inserted/updated/deletedij> 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/deletedij> -- 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 Typescreate table bitTable (id int, bv LONG VARCHAR FOR BIT DATA);0 rows inserted/updated/deletedij> 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 constructorinsert 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/deletedij> -- 5306: Bit -> Charcreate table charTable (id int, cv long varchar);0 rows inserted/updated/deletedij> 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 constructorinsert 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/deletedij> -- Verify that 5306 still works with Union.create table pt5 (b5 char(2) for bit data);0 rows inserted/updated/deletedij> create table pt10 (b10 char (4) for bit data);0 rows inserted/updated/deletedij> insert into pt10 values (x'01000110');1 row inserted/updated/deletedij> insert into pt5 values (x'1010');1 row inserted/updated/deletedij> 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/deletedij> drop table pt10;0 rows inserted/updated/deletedij> -- beetle 5612create table t5612 (c1 char(10), c2 varchar(10), c3 long varchar);0 rows inserted/updated/deletedij> 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-1085create table npetest1 (col1 varchar(36) for bit data not null, constraint pknpe1 primary key (col1));0 rows inserted/updated/deletedij> create table npetest2 (col2 varchar(36) for bit data, constraint fknpe1 foreign key (col2) references npetest1(col1) on delete cascade);0 rows inserted/updated/deletedij> insert into npetest1 (col1) values (X'0000000001');1 row inserted/updated/deletedij> insert into npetest1 (col1) values (X'0000000002');1 row inserted/updated/deletedij> insert into npetest1 (col1) values (X'0000000003');1 row inserted/updated/deletedij> insert into npetest2 (col2) values (X'0000000001');1 row inserted/updated/deletedij> insert into npetest2 (col2) values (NULL);1 row inserted/updated/deletedij> insert into npetest2 (col2) values (X'0000000002');1 row inserted/updated/deletedij> 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/deletedij> drop table npetest1;0 rows inserted/updated/deletedij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -