1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
|
--
-- BIT types
--
--
-- Build tables for testing
--
CREATE TABLE ZPBIT_TABLE(b BIT(11));
INSERT INTO ZPBIT_TABLE VALUES (B'');
INSERT INTO ZPBIT_TABLE VALUES (B'0');
INSERT INTO ZPBIT_TABLE VALUES (B'11011');
INSERT INTO ZPBIT_TABLE VALUES (B'01010101010');
INSERT INTO ZPBIT_TABLE VALUES (B'101011111010'); -- too long
--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 (B'0');
INSERT INTO VARBIT_TABLE VALUES (B'010101');
INSERT INTO VARBIT_TABLE VALUES (B'01010101010');
INSERT INTO VARBIT_TABLE VALUES (B'101011111010'); -- too long
--INSERT INTO VARBIT_TABLE VALUES ('X554');
--INSERT INTO VARBIT_TABLE VALUES ('X555');
SELECT * FROM VARBIT_TABLE;
-- Concatenation
SELECT v, b, (v || b) AS concat
FROM ZPBIT_TABLE, VARBIT_TABLE
ORDER BY 3;
-- Length
SELECT b, length(b) AS lb
FROM ZPBIT_TABLE;
SELECT v, length(v) AS lv
FROM VARBIT_TABLE;
-- Substring
SELECT b,
SUBSTRING(b FROM 2 FOR 4) AS sub_2_4,
SUBSTRING(b FROM 7 FOR 13) AS sub_7_13,
SUBSTRING(b FROM 6) AS sub_6
FROM ZPBIT_TABLE;
SELECT v,
SUBSTRING(v FROM 2 FOR 4) AS sub_2_4,
SUBSTRING(v FROM 7 FOR 13) AS sub_7_13,
SUBSTRING(v FROM 6) AS sub_6
FROM VARBIT_TABLE;
--- Bit operations
DROP TABLE varbit_table;
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
DROP TABLE zpbit_table;
CREATE TABLE zpbit_table (a BIT(16), b BIT(16));
COPY zpbit_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 zpbit_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 zpbit_table;
SELECT a,a<<4 AS "a<<4",b,b>>2 AS "b>>2" FROM zpbit_table;
DROP TABLE zpbit_table;
-- The following should fail
select B'001' & B'10';
select B'0111' | B'011';
select B'0010' # B'011101';
-- More position tests, checking all the boundary cases
SELECT POSITION(B'1010' IN B'0000101'); -- 0
SELECT POSITION(B'1010' IN B'00001010'); -- 5
SELECT POSITION(B'1010' IN B'00000101'); -- 0
SELECT POSITION(B'1010' IN B'000001010'); -- 6
SELECT POSITION(B'' IN B'00001010'); -- 1
SELECT POSITION(B'0' IN B''); -- 0
SELECT POSITION(B'' IN B''); -- 0
SELECT POSITION(B'101101' IN B'001011011011011000'); -- 3
SELECT POSITION(B'10110110' IN B'001011011011010'); -- 3
SELECT POSITION(B'1011011011011' IN B'001011011011011'); -- 3
SELECT POSITION(B'1011011011011' IN B'00001011011011011'); -- 5
SELECT POSITION(B'11101011' IN B'11101011'); -- 1
SELECT POSITION(B'11101011' IN B'011101011'); -- 2
SELECT POSITION(B'11101011' IN B'00011101011'); -- 4
SELECT POSITION(B'11101011' IN B'0000011101011'); -- 6
SELECT POSITION(B'111010110' IN B'111010110'); -- 1
SELECT POSITION(B'111010110' IN B'0111010110'); -- 2
SELECT POSITION(B'111010110' IN B'000111010110'); -- 4
SELECT POSITION(B'111010110' IN B'00000111010110'); -- 6
SELECT POSITION(B'111010110' IN B'11101011'); -- 0
SELECT POSITION(B'111010110' IN B'011101011'); -- 0
SELECT POSITION(B'111010110' IN B'00011101011'); -- 0
SELECT POSITION(B'111010110' IN B'0000011101011'); -- 0
SELECT POSITION(B'111010110' IN B'111010110'); -- 1
SELECT POSITION(B'111010110' IN B'0111010110'); -- 2
SELECT POSITION(B'111010110' IN B'000111010110'); -- 4
SELECT POSITION(B'111010110' IN B'00000111010110'); -- 6
SELECT POSITION(B'111010110' IN B'000001110101111101011'); -- 0
SELECT POSITION(B'111010110' IN B'0000001110101111101011'); -- 0
SELECT POSITION(B'111010110' IN B'000000001110101111101011'); -- 0
SELECT POSITION(B'111010110' IN B'00000000001110101111101011'); -- 0
SELECT POSITION(B'111010110' IN B'0000011101011111010110'); -- 14
SELECT POSITION(B'111010110' IN B'00000011101011111010110'); -- 15
SELECT POSITION(B'111010110' IN B'0000000011101011111010110'); -- 17
SELECT POSITION(B'111010110' IN B'000000000011101011111010110'); -- 19
SELECT POSITION(B'000000000011101011111010110' IN B'000000000011101011111010110'); -- 1
SELECT POSITION(B'00000000011101011111010110' IN B'000000000011101011111010110'); -- 2
SELECT POSITION(B'0000000000011101011111010110' IN B'000000000011101011111010110'); -- 0
-- Shifting
CREATE TABLE ZPBIT_SHIFT_TABLE(b BIT(16));
INSERT INTO ZPBIT_SHIFT_TABLE VALUES (B'11011');
INSERT INTO ZPBIT_SHIFT_TABLE SELECT b>>1 FROM ZPBIT_SHIFT_TABLE;
INSERT INTO ZPBIT_SHIFT_TABLE SELECT b>>2 FROM ZPBIT_SHIFT_TABLE;
INSERT INTO ZPBIT_SHIFT_TABLE SELECT b>>4 FROM ZPBIT_SHIFT_TABLE;
INSERT INTO ZPBIT_SHIFT_TABLE SELECT b>>8 FROM ZPBIT_SHIFT_TABLE;
SELECT POSITION(B'1101'IN b),
POSITION(B'11011' IN b),
b
FROM ZPBIT_SHIFT_TABLE ;
CREATE TABLE VARBIT_SHIFT_TABLE(v BIT VARYING(18));
INSERT INTO VARBIT_SHIFT_TABLE VALUES (B'11011');
INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v AS BIT(6)) >>1 FROM VARBIT_SHIFT_TABLE;
INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v AS BIT(8)) >>2 FROM VARBIT_SHIFT_TABLE;
INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v AS BIT(12)) >>4 FROM VARBIT_SHIFT_TABLE;
INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v AS BIT(20)) >>8 FROM VARBIT_SHIFT_TABLE;
SELECT POSITION(B'1101' IN v),
POSITION(B'11011' IN v),
v
FROM VARBIT_SHIFT_TABLE ;
DROP TABLE ZPBIT_SHIFT_TABLE;
DROP TABLE VARBIT_SHIFT_TABLE;
|
