github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/bit (about) 1 # LogicTest: !3node-tenant 2 query TTT 3 SELECT B'1000101'::BIT(4)::STRING, 4 B'1000101'::BIT(4), 5 B'1000101' 6 ---- 7 1000 1000 1000101 8 9 10 statement ok 11 CREATE TABLE bits ( 12 a BIT, b BIT(4), c VARBIT, d VARBIT(4), 13 FAMILY "primary" (a, b, c, d, rowid) 14 ) 15 16 query TT colnames 17 SHOW CREATE TABLE bits 18 ---- 19 table_name create_statement 20 bits CREATE TABLE bits ( 21 a BIT NULL, 22 b BIT(4) NULL, 23 c VARBIT NULL, 24 d VARBIT(4) NULL, 25 FAMILY "primary" (a, b, c, d, rowid) 26 ) 27 28 subtest bit_fixed1 29 30 statement ok 31 INSERT INTO bits(a) VALUES (B'1'), (B'0'); 32 33 statement error bit string length 0 does not match type BIT 34 INSERT INTO bits(a) VALUES (B'') 35 36 statement error bit string length 4 does not match type BIT 37 INSERT INTO bits(a) VALUES (B'1110') 38 39 subtest bit_fixed4 40 41 statement ok 42 INSERT INTO bits(b) VALUES (B'0000'), (B'1001'); 43 44 statement error bit string length 0 does not match type BIT\(4\) 45 INSERT INTO bits(b) VALUES (B'') 46 47 statement error bit string length 3 does not match type BIT\(4\) 48 INSERT INTO bits(b) VALUES (B'111') 49 50 statement error bit string length 9 does not match type BIT\(4\) 51 INSERT INTO bits(b) VALUES (B'111000111') 52 53 subtest bit_varying_unlimited 54 55 statement ok 56 INSERT INTO bits(c) VALUES (B'1'), (B'0'), (B''), (B'1110'), 57 (B'0101010101010101001101010101010101010101010101010101010101010101010010101') -- more than 64 bits 58 59 subtest bit_varying_limited 60 61 statement ok 62 INSERT INTO bits(d) VALUES (B'1'), (B'0'), (B''), (B'1110') 63 64 statement error bit string length 73 too large for type VARBIT\(4\) 65 INSERT INTO bits(d) VALUES 66 (B'0101010101010101001101010101010101010101010101010101010101010101010010101') -- more than 64 bits 67 68 subtest results 69 70 query TITITITI colnames 71 SELECT a, length(a::STRING) an, 72 b, length(b::STRING) bn, 73 c, length(c::STRING) cn, 74 d, length(d::STRING) dn 75 FROM bits 76 ORDER BY 1,2,3,4,5,6,7,8 77 ---- 78 a an b bn c cn d dn 79 NULL NULL NULL NULL NULL NULL · 0 80 NULL NULL NULL NULL NULL NULL 0 1 81 NULL NULL NULL NULL NULL NULL 1 1 82 NULL NULL NULL NULL NULL NULL 1110 4 83 NULL NULL NULL NULL · 0 NULL NULL 84 NULL NULL NULL NULL 0 1 NULL NULL 85 NULL NULL NULL NULL 0101010101010101001101010101010101010101010101010101010101010101010010101 73 NULL NULL 86 NULL NULL NULL NULL 1 1 NULL NULL 87 NULL NULL NULL NULL 1110 4 NULL NULL 88 NULL NULL 0000 4 NULL NULL NULL NULL 89 NULL NULL 1001 4 NULL NULL NULL NULL 90 0 1 NULL NULL NULL NULL NULL NULL 91 1 1 NULL NULL NULL NULL NULL NULL 92 93 subtest bit_arith 94 95 statement ok 96 INSERT INTO bits(b) VALUES (B'0110'), (B'0011') 97 98 statement ok 99 INSERT INTO bits(c) VALUES (B'1010'), (B'11') 100 101 # Shifts always truncate/pad to the bit array size. 102 query TTTTTTT colnames 103 SELECT x.b, 104 x.b << 0 AS l0, 105 x.b >> 0 AS r0, 106 x.b << -1 AS lm1, 107 x.b >> 1 AS r1, 108 x.b >> -1 AS rm11, 109 x.b << 1 AS l1 110 FROM bits x 111 WHERE x.b IS NOT NULL 112 ORDER BY 1,2,3,4,5,6,7 113 ---- 114 b l0 r0 lm1 r1 rm11 l1 115 0000 0000 0000 0000 0000 0000 0000 116 0011 0011 0011 0001 0001 0110 0110 117 0110 0110 0110 0011 0011 1100 1100 118 1001 1001 1001 0100 0100 0010 0010 119 120 # Concat works on mixed bit arrays. 121 query TTTT rowsort 122 SELECT x.b, y.c, x.b || y.c, y.c || x.b FROM bits x, bits y WHERE x.b IS NOT NULL AND length(y.c::string) < 5 123 ---- 124 0000 1 00001 10000 125 0000 0 00000 00000 126 0000 · 0000 0000 127 0000 1110 00001110 11100000 128 0000 1010 00001010 10100000 129 0000 11 000011 110000 130 1001 1 10011 11001 131 1001 0 10010 01001 132 1001 · 1001 1001 133 1001 1110 10011110 11101001 134 1001 1010 10011010 10101001 135 1001 11 100111 111001 136 0110 1 01101 10110 137 0110 0 01100 00110 138 0110 · 0110 0110 139 0110 1110 01101110 11100110 140 0110 1010 01101010 10100110 141 0110 11 011011 110110 142 0011 1 00111 10011 143 0011 0 00110 00011 144 0011 · 0011 0011 145 0011 1110 00111110 11100011 146 0011 1010 00111010 10100011 147 0011 11 001111 110011 148 149 query TT rowsort 150 SELECT x.b, ~x.b AS comp FROM bits x WHERE b IS NOT NULL 151 ---- 152 0000 1111 153 1001 0110 154 0110 1001 155 0011 1100 156 157 statement ok 158 DELETE FROM bits; INSERT INTO bits(c) VALUES (B'0'), (B'1') 159 160 query TT rowsort 161 SELECT x.c, ~x.c AS comp FROM bits x 162 ---- 163 0 1 164 1 0 165 166 query TTTTT rowsort 167 SELECT x.c AS v1, y.c AS v2, 168 x.c & y.c AS "and", 169 x.c | y.c AS "or", 170 x.c # y.c AS "xor" 171 FROM bits x, bits y 172 ---- 173 0 0 0 0 0 174 0 1 0 1 1 175 1 0 0 1 1 176 1 1 1 1 0 177 178 subtest bit_ordering 179 180 statement ok 181 CREATE TABLE obits(x VARBIT); 182 INSERT INTO obits(x) VALUES 183 (B'0'), 184 (B'1'), 185 (B'0000'), 186 (B'0001'), 187 (B'010'), 188 (B'10'), 189 (B'11'), 190 (B''), 191 (B'00100'), 192 (B'00110'), 193 (B'00001'), 194 (B'1001001010101'), 195 (B'01001001010101'), 196 (B'11001001010101') 197 198 # Check unindexed ordering. 199 query T 200 SELECT * FROM obits ORDER BY x 201 ---- 202 · 203 0 204 0000 205 00001 206 0001 207 00100 208 00110 209 010 210 01001001010101 211 1 212 10 213 1001001010101 214 11 215 11001001010101 216 217 # Check indexed ordering. 218 statement ok 219 CREATE INDEX obits_idx ON obits(x) 220 221 query T 222 SELECT * FROM obits@obits_idx ORDER BY x 223 ---- 224 · 225 0 226 0000 227 00001 228 0001 229 00100 230 00110 231 010 232 01001001010101 233 1 234 10 235 1001001010101 236 11 237 11001001010101 238 239 subtest bit_arrays 240 241 query TT colnames 242 SELECT ARRAY[B'101011'] AS a, '{111001}'::VARBIT[] AS b 243 ---- 244 a b 245 {101011} {111001} 246 247 statement ok 248 CREATE TABLE obitsa(x VARBIT(20)[]); 249 INSERT INTO obitsa(x) VALUES 250 (ARRAY[B'01', B'']), 251 (ARRAY[B'01', B'0']), 252 (ARRAY[B'01', B'1']), 253 (ARRAY[B'01', B'0000']), 254 (ARRAY[B'01', B'0001']), 255 (ARRAY[B'01', B'010']), 256 (ARRAY[B'01', B'10']), 257 (ARRAY[B'01', B'11']), 258 (ARRAY[B'01', B'']), 259 (ARRAY[B'01', B'00100']), 260 (ARRAY[B'01', B'00110']), 261 (ARRAY[B'01', B'00001']), 262 (ARRAY[B'01', B'1001001010101']), 263 (ARRAY[B'01', B'01001001010101']), 264 (ARRAY[B'01', B'11001001010101']) 265 266 query T 267 SELECT create_statement FROM [SHOW CREATE obitsa] 268 ---- 269 CREATE TABLE obitsa ( 270 x VARBIT(20)[] NULL, 271 FAMILY "primary" (x, rowid) 272 ) 273 274 # Check unindexed ordering. 275 query T rowsort 276 SELECT * FROM obitsa 277 ---- 278 {01,""} 279 {01,0} 280 {01,1} 281 {01,0000} 282 {01,0001} 283 {01,010} 284 {01,10} 285 {01,11} 286 {01,""} 287 {01,00100} 288 {01,00110} 289 {01,00001} 290 {01,1001001010101} 291 {01,01001001010101} 292 {01,11001001010101}