📄 bit.out
字号:
ij> ---- Test the builtin type 'bit'-- Specifically the base-16, hex bit literal-- stupid test of literalsvalues(X'aAff');1 ----aaffij> -- casting to a for bit data typevalues (cast (x'ee' as char(2) for bit data));1 ----ee20ij> values x'aAff' || (cast (x'ee' as char(2) for bit data));1 --------aaffee20ij> -- are the search conditions true?create table tab1 (c1 char(25));0 rows inserted/updated/deletedij> insert into tab1 values 'search condition is true';1 row inserted/updated/deletedij> 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/deletedij> -- simple negative testvalues(X'gg');ERROR 42X01: Syntax error: Encountered "\'gg\'" at line 2, column 9.ij> values(X'z');ERROR 42X01: Syntax error: Encountered "\'z\'" at line 1, column 9.ij> values(X'zz');ERROR 42X01: Syntax error: Encountered "\'zz\'" at line 1, column 9.ij> -- fails after bug 5742 is fixedvalues(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 expressionvalues({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 expressionvalues({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 expressionvalues({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 syntaxcreate 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/deletedij> drop table t1;0 rows inserted/updated/deletedij> 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/deletedij> drop table t1;0 rows inserted/updated/deletedij> 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/deletedij> -- explicit nullinsert into t values (null, null, null, null, null, null, null, null, null);1 row inserted/updated/deletedij> -- implicit nullinsert into t (i) values (null);1 row inserted/updated/deletedij> select b, bv, lbv from t;B |BV |LBV ------------------------------------------------------------------------------------------------------------------------------------------------------NULL|NULL |NULL NULL|NULL |NULL ij> -- sample datainsert into t values (0, 100, 'hello', 'everyone is here', 200.0e0, 200.0e0, X'12af', X'0000111100001111', X'abc123');1 row inserted/updated/deletedij> insert into t values (-1, -100, 'goodbye', 'everyone is there', -200.0e0, -200.0e0, X'0000', X'', X'10101010');1 row inserted/updated/deletedij> -- truncation -- should get an errorinsert 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/deletedij> insert into t (b, bv) values (X'', X'');1 row inserted/updated/deletedij> select b, bv from t;B |BV ---------------------NULL|NULL NULL|NULL 12af|00001111000011110000| 0120|01 2020| ij> drop table t;0 rows inserted/updated/deletedij> ---- simple comparisons-- returns 1 if the search conditions are true-- create table nulltab (b char(1) for bit data);0 rows inserted/updated/deletedij> insert into nulltab values (null);1 row inserted/updated/deletedij> 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/deletedij> ---- 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/deletedij> insert into t values (null, null, null, null, null, 'null', 'null columns');1 row inserted/updated/deletedij> insert into t values (X'', X'', X'', X'', X'', '0', 'zero length column');1 row inserted/updated/deletedij> insert into t values (X'0000000001', X'0000000001', X'01', X'01', X'0000000001', '1', '1');1 row inserted/updated/deletedij> insert into t values (X'0000000011', X'0000000011', X'03', X'03', X'03', '3', '3');1 row inserted/updated/deletedij> insert into t values (X'1111111111', X'1111111111', X'ff', X'ff', X'1111111111', 'ff', 'ff');1 row inserted/updated/deletedij> insert into t values (X'11', X'11', X'aa', X'aa', X'aa', 'aa', 'aa');1 row inserted/updated/deletedij> -- 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 runselect {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 ----------------------------------------2020202020202020202020202020202020202020000000001120202020202020202020202020202011111111112020202020202020202020202020201120202020202020202020202020202020202020ij> select b10 from t where b10 < X'0000000010';B10 ----------------------------------------0000000001202020202020202020202020202020ij> select b10 from t where b10 <= X'0000000011';B10 ----------------------------------------00000000012020202020202020202020202020200000000011202020202020202020202020202020ij> select b10 from t where b10 >= X'0000000011';⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -