--
-- BIT types
--

--
-- Build tables for testing
--

CREATE TABLE ZPBIT_TABLE(b BIT(11));

INSERT INTO ZPBIT_TABLE VALUES ('B');

INSERT INTO ZPBIT_TABLE VALUES ('B0');

INSERT INTO ZPBIT_TABLE VALUES ('B010101');

INSERT INTO ZPBIT_TABLE VALUES ('B01010101010');

INSERT INTO ZPBIT_TABLE VALUES ('B010101010101');

INSERT INTO ZPBIT_TABLE VALUES ('X554');

INSERT INTO ZPBIT_TABLE VALUES ('X555');

SELECT * FROM ZPBIT_TABLE; 

CREATE TABLE VARBIT_TABLE(v BIT VARYING(11));

INSERT INTO VARBIT_TABLE VALUES ('B');

INSERT INTO VARBIT_TABLE VALUES ('B0');

INSERT INTO VARBIT_TABLE VALUES ('B010101');

INSERT INTO VARBIT_TABLE VALUES ('B01010101010');

INSERT INTO VARBIT_TABLE VALUES ('B010101010101');

INSERT INTO VARBIT_TABLE VALUES ('X554');

INSERT INTO VARBIT_TABLE VALUES ('X555');

SELECT * FROM VARBIT_TABLE; 

-- Delete from tables
DROP TABLE ZPBIT_TABLE;
CREATE TABLE ZPBIT_TABLE(b BIT(16));

INSERT INTO ZPBIT_TABLE VALUES ('B11011');
INSERT INTO ZPBIT_TABLE SELECT b>>1 FROM ZPBIT_TABLE;
INSERT INTO ZPBIT_TABLE SELECT b>>2 FROM ZPBIT_TABLE;
INSERT INTO ZPBIT_TABLE SELECT b>>4 FROM ZPBIT_TABLE;
INSERT INTO ZPBIT_TABLE SELECT b>>8 FROM ZPBIT_TABLE;
SELECT POSITION('B1101'::bit IN b) as pos, 
       POSITION('B11011'::bit IN b) as pos,
       b 
       FROM ZPBIT_TABLE ;

DROP TABLE VARBIT_TABLE;
CREATE TABLE VARBIT_TABLE(v BIT VARYING(19));
INSERT INTO VARBIT_TABLE VALUES ('B11011');
INSERT INTO VARBIT_TABLE SELECT v>>1 FROM VARBIT_TABLE;
INSERT INTO VARBIT_TABLE SELECT v>>2 FROM VARBIT_TABLE;
INSERT INTO VARBIT_TABLE SELECT v>>4 FROM VARBIT_TABLE;
INSERT INTO VARBIT_TABLE SELECT v>>8 FROM VARBIT_TABLE;
SELECT POSITION('B1101'::bit IN v) as pos, 
       POSITION('B11011'::bit IN v) as pos,
       v 
       FROM VARBIT_TABLE ;

-- Concatenation
SELECT v, b::varbit AS B, (v || b)::varbit AS C 
       FROM ZPBIT_TABLE, VARBIT_TABLE 
       WHERE v::bit(16)=b
       ORDER BY C;

-- Length
SELECT b, length(b) AS lb
       FROM ZPBIT_TABLE;
SELECT v, length(v) AS lv
       FROM VARBIT_TABLE;

-- Substring
SELECT b::varbit,
       SUBSTRING(b FROM 2 FOR 4)::varbit AS sub1,
       SUBSTRING(b FROM 7 FOR 13)::varbit AS sub2,
       SUBSTRING(b FROM 6)::varbit AS sub3
       FROM ZPBIT_TABLE;
SELECT v,
       SUBSTRING(v FROM 2 FOR 4)::varbit AS sub1,
       SUBSTRING(v FROM 7 FOR 13)::varbit AS sub2,
       SUBSTRING(v FROM 6)::varbit AS sub3
       FROM VARBIT_TABLE;

-- Drop the tables
DROP TABLE ZPBIT_TABLE;
DROP TABLE VARBIT_TABLE;

--- Bit operations
CREATE TABLE varbit_table (a BIT VARYING(16), b BIT VARYING(16));
COPY varbit_table FROM stdin;
X0F	X10
X1F	X11
X2F	X12
X3F	X13
X8F	X04
X000F	X0010
X0123	XFFFF
X2468	X2468
XFA50	X05AF
X1234	XFFF5
\.

SELECT a,b,~a AS "~ a",a & b AS "a & b", 
	a|b AS "a | b", a^b AS "a ^ b" FROM varbit_table;
SELECT a,b,a<b AS "a<b",a<=b AS "a<=b",a=b AS "a=b",
        a>=b AS "a>=b",a>b AS "a>b",a<=>b AS "a<=>b" FROM varbit_table;
SELECT a,a<<4 AS "a<<4",b,b>>2 AS "b>>2" FROM varbit_table;

DROP TABLE varbit_table;

--- Bit operations
CREATE TABLE zpbit (a BIT(16), b BIT(16));
COPY zpbit FROM stdin;
X0F	X10
X1F	X11
X2F	X12
X3F	X13
X8F	X04
X000F	X0010
X0123	XFFFF
X2468	X2468
XFA50	X05AF
X1234	XFFF5
\.

SELECT a,b,~a AS "~ a",a & b AS "a & b", 
	a|b AS "a | b", a^b AS "a ^ b" FROM zpbit;
SELECT a,b,a<b AS "a<b",a<=b AS "a<=b",a=b AS "a=b",
        a>=b AS "a>=b",a>b AS "a>b",a<>b AS "a<>b" FROM zpbit;
SELECT a,a<<4 AS "a<<4",b,b>>2 AS "b>>2" FROM zpbit;

DROP TABLE zpbit;


-- The following should fail
select 'X123'::bit & 'X12'::bit;
select 'B0111'::bit | 'B011'::bit;
select 'X023'::bit ^ 'B011101'::bit;

-- More position tests, checking all the boundary cases
SELECT POSITION('B1010'::bit IN 'B0000101'::bit);   -- 0
SELECT POSITION('B1010'::bit IN 'B00001010'::bit);  -- 5
SELECT POSITION('B1010'::bit IN 'B00000101'::bit);  -- 0
SELECT POSITION('B1010'::bit IN 'B000001010'::bit);  -- 6

SELECT POSITION('B'::bit IN 'B00001010'::bit);  -- 1
SELECT POSITION('B0'::bit IN 'B'::bit);  -- 0
SELECT POSITION('B'::bit IN 'B'::bit);  -- 0
SELECT POSITION('B101101'::bit IN 'B001011011011011000'::bit);  -- 3
SELECT POSITION('B10110110'::bit IN 'B001011011011010'::bit);  -- 3
SELECT POSITION('B1011011011011'::bit IN 'B001011011011011'::bit);  -- 3
SELECT POSITION('B1011011011011'::bit IN 'B00001011011011011'::bit);  -- 5

SELECT POSITION('B11101011'::bit IN 'B11101011'::bit); -- 1
SELECT POSITION('B11101011'::bit IN 'B011101011'::bit); -- 2
SELECT POSITION('B11101011'::bit IN 'B00011101011'::bit); -- 4
SELECT POSITION('B11101011'::bit IN 'B0000011101011'::bit); -- 6

SELECT POSITION('B111010110'::bit IN 'B111010110'::bit); -- 1
SELECT POSITION('B111010110'::bit IN 'B0111010110'::bit); -- 2
SELECT POSITION('B111010110'::bit IN 'B000111010110'::bit); -- 4
SELECT POSITION('B111010110'::bit IN 'B00000111010110'::bit); -- 6

SELECT POSITION('B111010110'::bit IN 'B11101011'::bit); -- 0
SELECT POSITION('B111010110'::bit IN 'B011101011'::bit); -- 0
SELECT POSITION('B111010110'::bit IN 'B00011101011'::bit); -- 0
SELECT POSITION('B111010110'::bit IN 'B0000011101011'::bit); -- 0

SELECT POSITION('B111010110'::bit IN 'B111010110'::bit); -- 1
SELECT POSITION('B111010110'::bit IN 'B0111010110'::bit); -- 2
SELECT POSITION('B111010110'::bit IN 'B000111010110'::bit); -- 4
SELECT POSITION('B111010110'::bit IN 'B00000111010110'::bit); -- 6

SELECT POSITION('B111010110'::bit IN 'B000001110101111101011'::bit); -- 0
SELECT POSITION('B111010110'::bit IN 'B0000001110101111101011'::bit); -- 0
SELECT POSITION('B111010110'::bit IN 'B000000001110101111101011'::bit); -- 0
SELECT POSITION('B111010110'::bit IN 'B00000000001110101111101011'::bit); -- 0

SELECT POSITION('B111010110'::bit IN 'B0000011101011111010110'::bit); -- 14
SELECT POSITION('B111010110'::bit IN 'B00000011101011111010110'::bit); -- 15
SELECT POSITION('B111010110'::bit IN 'B0000000011101011111010110'::bit); -- 17
SELECT POSITION('B111010110'::bit IN 'B000000000011101011111010110'::bit); -- 19

SELECT POSITION('B000000000011101011111010110'::bit IN 'B000000000011101011111010110'::bit); -- 1
SELECT POSITION('B00000000011101011111010110'::bit IN 'B000000000011101011111010110'::bit); -- 2
SELECT POSITION('B0000000000011101011111010110'::bit IN 'B000000000011101011111010110'::bit); -- 0