| -- |
| -- 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'); |
| |
| -- casting to a for bit data type |
| values (cast (x'ee' as char(2) for bit data)); |
| values x'aAff' || (cast (x'ee' as char(2) for bit data)); |
| |
| -- are the search conditions true? |
| create table tab1 (c1 char(25)); |
| insert into tab1 values 'search condition is true'; |
| select * from tab1 where ((X'1010' || X'0011' || X'0100') = X'101000110100'); |
| select * From tab1 where ((X'1010' || X'0011' || X'0100') = X'101000110100'); |
| select * from tab1 where (X'1100' > X'0011'); |
| drop table tab1; |
| |
| -- simple negative test |
| values(X'gg'); |
| values(X'z'); |
| values(X'zz'); |
| -- fails after bug 5742 is fixed |
| values(X'9'); |
| |
| -- some quick tests of the length function |
| -- # bits in a string expression |
| values({fn length(X'ab')} * 8); |
| values({fn length(X'11')} * 8); |
| -- # characters in a string expression |
| values({fn length(X'ab')}); |
| values({fn length(X'11')}); |
| -- # octets in a string expression |
| values({fn length(X'ab')}); |
| values({fn length(X'11')}); |
| |
| -- 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); |
| drop table t1; |
| 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); |
| drop table t1; |
| |
| 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); |
| |
| -- explicit null |
| insert into t values (null, null, null, null, null, null, null, null, null); |
| |
| -- implicit null |
| insert into t (i) values (null); |
| select b, bv, lbv from t; |
| |
| -- sample data |
| insert into t values (0, 100, 'hello', 'everyone is here', 200.0e0, 200.0e0, |
| X'12af', X'0000111100001111', X'abc123'); |
| insert into t values (-1, -100, 'goodbye', 'everyone is there', -200.0e0, -200.0e0, |
| X'0000', X'', X'10101010'); |
| |
| -- truncation -- should get an error |
| insert into t (b, bv) values (X'ffffffff', X'ffffffff'); |
| select b, bv, lbv from t; |
| |
| -- padding -- will be warning, some day (not now) |
| insert into t (b, bv) values (X'01', X'01'); |
| insert into t (b, bv) values (X'', X''); |
| |
| select b, bv from t; |
| drop table t; |
| |
| -- |
| -- simple comparisons |
| -- returns 1 if the search conditions are true |
| -- |
| create table nulltab (b char(1) for bit data); |
| insert into nulltab values (null); |
| |
| select 1 from nulltab where X'0001' > X'0000'; |
| select 1 from nulltab where X'0100' > X'0001'; |
| select 1 from nulltab where X'ff00' > X'00ff'; |
| select 1 from nulltab where X'0100' > X'0100'; |
| select 1 from nulltab where X'0100' > b; |
| |
| select 1 from nulltab where X'0001' >= X'0000'; |
| select 1 from nulltab where X'0100' >= X'0001'; |
| select 1 from nulltab where X'ff00' >= X'00ff'; |
| select 1 from nulltab where X'0100' >= b; |
| |
| select 1 from nulltab where X'0001' < X'0000'; |
| select 1 from nulltab where X'0100' < X'0001'; |
| select 1 from nulltab where X'ff00' < X'00ff'; |
| select 1 from nulltab where X'0100' < b; |
| |
| select 1 from nulltab where X'0001' <= X'0000'; |
| select 1 from nulltab where X'0100' <= X'0001'; |
| select 1 from nulltab where X'ff00' <= X'00ff'; |
| select 1 from nulltab where X'0100' <= b; |
| |
| drop table nulltab; |
| |
| -- |
| -- 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)); |
| insert into t values (null, null, null, null, null, 'null', 'null columns'); |
| insert into t values (X'', X'', X'', X'', X'', '0', 'zero length column'); |
| insert into t values (X'0000000001', X'0000000001', X'01', X'01', X'0000000001', '1', '1'); |
| insert into t values (X'0000000011', X'0000000011', X'03', X'03', X'03', '3', '3'); |
| insert into t values (X'1111111111', X'1111111111', X'ff', X'ff', X'1111111111', 'ff', 'ff'); |
| insert into t values (X'11', X'11', X'aa', X'aa', X'aa', 'aa', 'aa'); |
| |
| -- 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; |
| select {fn length(cast(vb10 as char(10)))} from t where vb10 is not null; |
| select {fn length(cast(lbv as char(10)))} from t where vb10 is not null; |
| |
| select b10, c20, cv20 from t order by b10 asc; |
| select b10, c20, cv20 from t order by b10 desc; |
| select vb10, c20, cv20 from t order by vb10; |
| select b16, c20, cv20 from t order by b16; |
| select vb16, c20, cv20 from t order by vb16; |
| select vb16, c20, cv20, lbv from t order by lbv; |
| |
| select b10 from t where b10 > X'0000000010'; |
| select b10 from t where b10 < X'0000000010'; |
| select b10 from t where b10 <= X'0000000011'; |
| select b10 from t where b10 >= X'0000000011'; |
| select b10 from t where b10 <> X'0000000011'; |
| |
| select vb10 from t where vb10 > X'0000000010'; |
| select vb10 from t where vb10 < X'0000000010'; |
| select vb10 from t where vb10 <= X'0000000011'; |
| select vb10 from t where vb10 >= X'0000000011'; |
| select vb10 from t where vb10 <> X'0000000011'; |
| |
| select b16 from t where b16 > X'0000000010'; |
| select b16 from t where b16 < X'0000000010'; |
| select b16 from t where b16 <= X'0000000011'; |
| select b16 from t where b16 >= X'0000000011'; |
| select b16 from t where b16 <> X'0000000011'; |
| |
| select vb16 from t where vb16 > X'0000000010'; |
| select vb16 from t where vb16 < X'0000000010'; |
| select vb16 from t where vb16 <= X'0000000011'; |
| select vb16 from t where vb16 >= X'0000000011'; |
| select vb16 from t where vb16 <> X'0000000011'; |
| |
| select lbv from t where lbv > X'0000000010'; |
| select lbv from t where lbv < X'0000000010'; |
| select lbv from t where lbv <= X'0000000011'; |
| select lbv from t where lbv >= X'0000000011'; |
| select lbv from t where lbv <> X'0000000011'; |
| |
| select b10, vb10||X'11' from t where vb10||X'11' > b10; |
| select b10, X'11'||vb10 from t where X'11'||vb10 > b10; |
| select b16, vb16||X'11' from t where vb16||X'11' > b16; |
| |
| select b10 || vb10 from t; |
| select lbv || b10 from t; |
| select b10 || lbv from t; |
| select lbv || vb10 from t; |
| select vb10 || lbv from t; |
| |
| select t1.b10 from t t1, t t2 where t1.b10 > t2.b10; |
| |
| -- FUNCTIONS |
| -- some length functions |
| select {fn length(b10)} from t; |
| select {fn length(vb10)} from t; |
| select {fn length(lbv)} from t; |
| |
| select {fn length(c20)} from t; |
| select {fn length(cv20)} from t; |
| |
| drop table t; |
| |
| ----------------------- |
| -- test normalization |
| ----------------------- |
| create table t1 (c1 char(2) for bit data); |
| insert into t1 values (X'0001'); |
| insert into t1 values (X'0010'); |
| insert into t1 values (X'0011'); |
| select * from t1; |
| |
| -- now insert something that needs to be expanded |
| insert into t1 values (X'11'); |
| select * from t1; |
| |
| -- insert select, expand 1 byte |
| create table t2 (c1 char(3) for bit data); |
| insert into t2 select c1 from t1; |
| select * from t2; |
| drop table t2; |
| |
| -- insert select, expand many bytes |
| create table t2 (c1 char(20) for bit data); |
| insert into t2 select c1 from t1; |
| select * from t2; |
| drop table t2; |
| |
| drop table t1; |
| |
| -- |
| -- some extra tests for truncation. in 2.0 |
| create table t1 (b1 char(1) for bit data); |
| |
| -- ok |
| insert into t1 values (X'11'); |
| |
| -- valid length |
| insert into t1 values (X'10'); |
| insert into t1 values (X'11'); |
| |
| -- truncation errors |
| insert into t1 values (X'1000'); |
| insert into t1 values (X'100000'); |
| insert into t1 values (X'10000000'); |
| insert into t1 values (X'1000000000'); |
| insert into t1 values (X'100001'); |
| insert into t1 values (X'0001'); |
| insert into t1 values (X'8001'); |
| insert into t1 values (X'8000'); |
| |
| drop table t1; |
| create table t1 (b9 char(2) for bit data); |
| |
| -- ok |
| insert into t1 values (X'1111'); |
| |
| -- truncation errors |
| insert into t1 values (X'111100'); |
| insert into t1 values (X'11110000'); |
| insert into t1 values (X'1111000000'); |
| |
| insert into t1 values (X'1111111100000000'); |
| insert into t1 values (X'1111111111'); |
| insert into t1 values (X'11111111100001'); |
| insert into t1 values (X'0001'); |
| insert into t1 values (X'8001'); |
| insert into t1 values (X'8000'); |
| |
| drop table t1; |
| |
| -- 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); |
| |
| -- ok |
| insert into t1 values |
| ( |
| X'1111', |
| X'11111111', |
| X'1111111111', |
| X'1111111111111111', |
| X'111111111111111111' |
| ); |
| |
| -- ok |
| insert into t1 values |
| ( |
| X'1110', |
| X'11111110', |
| X'11111111', |
| X'1111111111111110', |
| X'1111111111111111' |
| ); |
| |
| -- bad |
| -- truncation error for column b8 |
| insert into t1 values |
| ( |
| null, |
| null, |
| X'111111111110', |
| null, |
| null |
| ); |
| |
| -- truncation error for column b7 |
| insert into t1 values |
| ( |
| null, |
| X'1111111100', |
| null, |
| null, |
| null |
| ); |
| |
| -- truncation error for column b7 |
| insert into t1 values |
| ( |
| null, |
| X'1111111111', |
| null, |
| null, |
| null |
| ); |
| |
| -- truncation error for column b15 |
| insert into t1 values |
| ( |
| null, |
| null, |
| null, |
| X'111111111111111100', |
| null |
| ); |
| |
| -- truncation error for column b15 |
| insert into t1 values |
| ( |
| null, |
| null, |
| null, |
| X'111111111111111111', |
| null |
| ); |
| |
| -- truncation error for column b16 |
| insert into t1 values |
| ( |
| null, |
| null, |
| null, |
| null, |
| X'11111111111111111110' |
| ); |
| |
| |
| AUTOCOMMIT OFF; |
| |
| -- bug 5160 - incorrect typing of VALUES table constructor on an insert; |
| |
| create table iv (id int, vc varchar(12)); |
| insert into iv values (1, 'abc'), (2, 'defghijk'), (3, 'lmnopqrstcc'); |
| insert into iv values (4, null), (5, 'null ok?'), (6, '2blanks '); |
| insert into iv values (7, 'dddd'), (8, '0123456789123'), (9, 'too long'); |
| select id, vc, {fn length(vc)} AS LEN from iv order by 1; |
| |
| -- 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); |
| select id, vc, {fn length(vc)} AS LEN from iv order by 1; |
| |
| -- 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); |
| |
| drop table iv; |
| |
| create table bv (id int, vb varchar(16) for bit data); |
| insert into bv values (1, X'1a'), (2, X'cafebabe'), (3, null); |
| select id, vb, {fn length(vb)} AS LEN from bv order by 1; |
| drop table bv; |
| |
| create table dv (id int, vc varchar(12)); |
| -- 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); |
| -- should pass |
| insert into dv values (1, '1.2'), (2, '34.5639'), (3, null); |
| select id, vc from dv order by 1; |
| drop table dv; |
| |
| -- 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); |
| insert into bitTable values (1, X'031'), (2, X'032'), (3, X''); |
| insert into bitTable values (4, null), (5, X'033'), (6, X'2020'); |
| select id, bv, {fn length(bv)} as LEN from bitTable order by 1; |
| |
| -- 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); |
| select id, bv, {fn length(bv)} AS LEN from bitTable order by 1; |
| |
| drop table bitTable; |
| |
| -- 5306: Bit -> Char |
| |
| create table charTable (id int, cv long varchar); |
| insert into charTable values (1, x'0101'), (2, x'00101100101001'), (3, x''); |
| insert into charTable values (4, null), (5, x'1010101111'), (6, x'1000'); |
| select id, cv, {fn length(cv)} as LEN from charTable order by 1; |
| |
| -- 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); |
| select id, cv, {fn length(cv)} AS LEN from charTable order by 1; |
| |
| drop table charTable; |
| |
| -- Verify that 5306 still works with Union. |
| |
| create table pt5 (b5 char(2) for bit data); |
| create table pt10 (b10 char (4) for bit data); |
| insert into pt10 values (x'01000110'); |
| insert into pt5 values (x'1010'); |
| select {fn length(CM)} from (select b5 from pt5 union all select b10 from pt10) as t(CM); |
| drop table pt5; |
| drop table pt10; |
| |
| -- beetle 5612 |
| |
| create table t5612 (c1 char(10), c2 varchar(10), c3 long varchar); |
| insert into t5612 values (X'00680069', X'00680069', X'00680069'); |
| select * from t5612; |
| values cast(X'00680069' as char(30)), cast(X'00680069' as varchar(30)), cast(X'00680069' as long varchar); |
| |
| -- DERBY-1085 |
| |
| create table npetest1 (col1 varchar(36) for bit data not null, constraint pknpe1 primary key (col1)); |
| create table npetest2 (col2 varchar(36) for bit data, constraint fknpe1 foreign key (col2) references npetest1(col1) on delete cascade); |
| insert into npetest1 (col1) values (X'0000000001'); |
| insert into npetest1 (col1) values (X'0000000002'); |
| insert into npetest1 (col1) values (X'0000000003'); |
| insert into npetest2 (col2) values (X'0000000001'); |
| insert into npetest2 (col2) values (NULL); |
| insert into npetest2 (col2) values (X'0000000002'); |
| select col1 from npetest1 where col1 not in (select col2 from npetest2); |
| select col1 from npetest1 where col1 not in (select col2 from npetest2 where col2 is not null); |
| |
| drop table npetest2; |
| drop table npetest1; |