github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/operator/bit_operator.result (about) 1 SELECT 1 ^ 1, 9 &4& 2, 1 ^ 0; 2 1 ^ 1 9 & 4 & 2 1 ^ 0 3 0 0 1 4 SELECT 29 & 15; 5 29 & 15 6 13 7 SELECT ~0, 64 << 2, '40' << 2; 8 ~0 64 << 2 40 << 2 9 18446744073709551615 256 160 10 SELECT 1 << 2; 11 1 << 2 12 4 13 SELECT 4 >> 2; 14 4 >> 2 15 1 16 SELECT 100 << ABS(-3); 17 100 << abs(-3) 18 800 19 SELECT BIN(~1); 20 bin(~1) 21 1111111111111111111111111111111111111111111111111111111111111110 22 SELECT 3 & ~8; 23 Data truncation: data out of range: data type int64, value '18446744073709551607' 24 DROP TABLE IF EXISTS t1; 25 CREATE TABLE t1( 26 tiny TINYINT NOT NULL, 27 small SMALLINT NOT NULL, 28 int_test INT NOT NULL, 29 big BIGINT NOT NULL 30 ); 31 INSERT INTO t1(tiny, small, int_test, big) VALUES(0, SIN(1), 1, -0); 32 INSERT INTO t1() VALUES('2', 32767, '1', 9223372036854775807); 33 SELECT tiny & small FROM t1; 34 tiny & small 35 0 36 2 37 SELECT tiny << small FROM t1; 38 tiny << small 39 0 40 0 41 SELECT tiny >> big FROM t1; 42 tiny >> big 43 0 44 0 45 DROP TABLE IF EXISTS t1; 46 CREATE TABLE t1( 47 tiny TINYINT UNSIGNED NOT NULL, 48 small SMALLINT UNSIGNED NOT NULL, 49 int_test INT UNSIGNED NOT NULL, 50 big BIGINT UNSIGNED NOT NULL 51 ); 52 INSERT INTO t1(tiny, small, int_test, big) VALUES(0, SIN(1)-COS(0), 1, 9223372036854775807); 53 INSERT INTO t1() VALUES(1, 927, LENGTH('abcd'), 90); 54 SELECT big >> COS(0) FROM t1; 55 big >> cos(0) 56 4611686018427387903 57 45 58 SELECT tiny & int_test | small ^ big FROM t1; 59 tiny & int_test | small ^ big 60 9223372036854775807 61 965 62 SELECT tiny << int_test | small >> big FROM t1; 63 tiny << int_test | small >> big 64 0 65 16 66 SELECT tiny << LENGTH('abcdefghijklmnopqrst') FROM t1 ORDER BY tiny; 67 tiny << length(abcdefghijklmnopqrst) 68 0 69 1048576 70 DROP TABLE IF EXISTS t1; 71 CREATE TABLE t1( 72 int_test INT UNSIGNED NOT NULL, 73 float_32 FLOAT NOT NULL, 74 float_64 DOUBLE NOT NULL 75 ); 76 INSERT INTO t1() VALUES(1, 0.0, 123.146484666486456); 77 INSERT INTO t1() VALUES('99', 0.000001, 1.0); 78 SELECT int_test & float_32 FROM t1; 79 int_test & float_32 80 0 81 0 82 SELECT float_32 | float_64 FROM t1; 83 float_32 | float_64 84 123 85 1 86 SELECT float_32 ^ float_64 + 11 FROM t1; 87 float_32 ^ float_64 + 11 88 134 89 12 90 SELECT float_32 >> int_test FROM t1; 91 float_32 >> int_test 92 0 93 0 94 DROP TABLE IF EXISTS t1; 95 CREATE TABLE t1( 96 int_test INT UNSIGNED NOT NULL, 97 float_32 FLOAT NOT NULL, 98 d1 DECIMAL NOT NULL 99 ); 100 INSERT INTO t1() VALUES(1, 0.000001, 1.00000000000000000000000001); 101 INSERT INTO t1() VALUES(YEAR('2022-02-02'), SIN(1), LENGTH('abcdefghijk') - MONTH('2022-09-09')); 102 SELECT ~float_32 & float_32 FROM t1; 103 invalid argument operator unary_tilde, bad value [FLOAT] 104 DROP TABLE IF EXISTS t1; 105 CREATE TABLE t1( 106 str1 CHAR(10), 107 str2 VARCHAR(10) 108 ); 109 INSERT INTO t1() VALUES('abc', '123'); 110 INSERT INTO t1() VALUES(NULL, 'dc'); 111 SELECT LENGTH(str1) | LENGTH(str2) FROM t1; 112 length(str1) | length(str2) 113 3 114 null 115 SELECT cast(STARTSWITH(str1, 'a') as int) | LENGTH(str2) FROM t1; 116 cast(startswith(str1, a) as int) | length(str2) 117 3 118 null 119 DELETE FROM t1; 120 INSERT INTO t1() VALUES('123', 23), (LENGTH('abc'), 0), ('0', NULL); 121 SELECT str1 << '1' FROM t1; 122 str1 << 1 123 246 124 6 125 0 126 SELECT str1 & '0' FROM t1; 127 str1 & 0 128 0 129 0 130 0 131 SELECT str1 & '11111' | '000101' & BIN(12) FROM t1; 132 str1 & 11111 | 000101 & bin(12) 133 103 134 71 135 68 136 SELECT str1 & '5555' | SPACE(100)+'1' & BIN(16) FROM t1; 137 str1 & 5555 | space(100) + 1 & bin(16) 138 51 139 3 140 0 141 SELECT str1 ^ '000000000000000'+'1'+'000000000000000' & '000000000000000'+'1'+'000000000000000' FROM t1; 142 str1 ^ 000000000000000 + 1 + 000000000000000 & 000000000000000 + 1 + 000000000000000 143 0 144 0 145 0 146 DROP TABLE IF EXISTS t1; 147 DROP TABLE IF EXISTS t2; 148 CREATE TABLE t1( 149 str1 VARCHAR(10), 150 PRIMARY KEY (str1) 151 ); 152 CREATE TABLE t2( 153 n1 INT, 154 PRIMARY KEY (n1) 155 ); 156 INSERT INTO t1() VALUES('101'),('-1'),(TRUE),(FALSE); 157 INSERT INTO t2() VALUES(101),(-1),(FALSE),(TRUE); 158 SELECT str1 & n1 FROM t1,t2 LIMIT 4; 159 str1 & n1 160 101 161 101 162 0 163 1 164 SELECT str1 & ABS(-SIN(7)) FROM t1; 165 str1 & abs(-sin(7)) 166 1 167 1 168 1 169 0 170 SELECT n1 & str1 & n1 & '111' & n1 & '1001' FROM t1,t2; 171 n1 & str1 & n1 & 111 & n1 & 1001 172 97 173 97 174 0 175 1 176 97 177 105 178 0 179 1 180 1 181 1 182 0 183 1 184 0 185 0 186 0 187 0 188 SELECT 1 << n1 FROM t2; 189 1 << n1 190 0 191 0 192 1 193 2 194 SELECT n1 << n1 >> n1 FROM t2; 195 n1 << n1 >> n1 196 0 197 0 198 0 199 1 200 SELECT n1 ^ str1 | n1 & str1 >> n1 << str1 FROM t1,t2; 201 n1 ^ str1 | n1 & str1 >> n1 << str1 202 0 203 -102 204 101 205 100 206 -102 207 0 208 -1 209 -2 210 100 211 -2 212 1 213 0 214 101 215 -1 216 0 217 1 218 SELECT n1 ^ 1 | n1 & '111' >> n1 << '1001' FROM t1,t2; 219 n1 ^ 1 | n1 & 111 >> n1 << 1001 220 100 221 -2 222 1 223 0 224 100 225 -2 226 1 227 0 228 100 229 -2 230 1 231 0 232 100 233 -2 234 1 235 0 236 SELECT '0150' | str1 | n1 | '000111' | n1 | '101010' FROM t1,t2; 237 0150 | str1 | n1 | 000111 | n1 | 101010 238 101119 239 -1 240 101119 241 101119 242 -1 243 -1 244 -1 245 -1 246 101119 247 -1 248 101119 249 101119 250 101119 251 -1 252 101119 253 101119 254 DROP TABLE IF EXISTS t1; 255 DROP TABLE IF EXISTS t2; 256 CREATE TABLE t1( 257 id INT, 258 str1 VARCHAR(10), 259 PRIMARY KEY (id) 260 ); 261 CREATE TABLE t2( 262 id INT, 263 n1 INT, 264 PRIMARY KEY (id) 265 ); 266 INSERT INTO t1() VALUES(1, '1'), (2, 'red'), (3, 'United'), (4, FALSE); 267 INSERT INTO t2() VALUES(1, 101), (2, 01010), (4, -1); 268 SELECT str1 | n1 FROM t1, t2 WHERE t1.id = t2.id AND t1.id = 1; 269 str1 | n1 270 101 271 UPDATE t1 JOIN t2 ON t1.id = t2.id SET str1 = n1 << 2; 272 UPDATE t1,t2 SET str1 = 2 >> str1, n1 = 3 >> 3 WHERE t1.id = 1; 273 INSERT INTO t1() VALUES(2 << 4, 'shift'), (3 & 0, 'bit'); 274 INSERT INTO t1 SELECT 2 << 8, 'UK'; 275 SELECT * FROM t1; 276 id str1 277 3 United 278 2 4040 279 4 -4 280 1 0 281 32 shift 282 0 bit 283 512 UK 284 DROP TABLE IF EXISTS t1; 285 DROP TABLE IF EXISTS t2; 286 CREATE TABLE t1( 287 id INT, 288 class VARCHAR(10), 289 name VARCHAR(10), 290 PRIMARY KEY (id) 291 ); 292 CREATE TABLE t2( 293 id INT, 294 grade VARCHAR(10), 295 score FLOAT, 296 PRIMARY KEY (id) 297 ); 298 INSERT INTO t1() VALUES(1,'c1','nion'), (2,'c2','unitd'), (3,'c1','jake'), (4,'c2','hadd'), (5,'c3','laik'); 299 INSERT INTO t2() VALUES(1,'A',70.1), (2,'B',59.3), (3,'C',81.2), (4,'B',48.3), (5,'C',99.4); 300 SELECT id,MAX(score),grade FROM t2 GROUP BY id,grade HAVING id > (2 << 2); 301 id max(score) grade 302 SELECT t1.id, t1.name, t2.grade FROM t1, t2 WHERE t1.id = t2.id AND t1.id < (2 << 2); 303 id name grade 304 1 nion A 305 2 unitd B 306 3 jake C 307 4 hadd B 308 5 laik C 309 SELECT score FROM t2 WHERE t2.score BETWEEN 2 << 5 AND 2 << 8; 310 score 311 70.1 312 81.2 313 99.4 314 DROP TABLE IF EXISTS t1; 315 CREATE TABLE t1( 316 id INT, 317 str1 VARCHAR(10), 318 PRIMARY KEY (id) 319 ); 320 INSERT INTO t1() VALUES(1,'c1'), (2,'11'), (3,'cd'), (4,'df'); 321 SELECT id | BIN(100), id & HEX(100) FROM t1; 322 id | bin(100) id & hex(100) 323 1100101 0 324 1100102 0 325 1100103 0 326 1100100 0 327 INSERT INTO t1(id) VALUES(0 & BIN(4)); 328 INSERT INTO t1(id) VALUES(-1 & HEX(8)); 329 SELECT id << HEX(88), id >> BIN(88) FROM t1; 330 id << hex(88) id >> bin(88) 331 288230376151711744 0 332 576460752303423488 0 333 864691128455135232 0 334 1152921504606846976 0 335 0 0 336 2305843009213693952 0 337 SELECT HEX(88) & BIN(88), BIN(id) | BIN(88) FROM t1; 338 hex(88) & bin(88) bin(id) | bin(88) 339 56 1011001 340 56 1011002 341 56 1011003 342 56 1011068 343 56 1011000 344 56 1011704 345 DROP TABLE IF EXISTS t1; 346 CREATE TABLE t1( 347 str1 VARCHAR(10), 348 PRIMARY KEY (str1) 349 ); 350 INSERT INTO t1() VALUES ('111'), ('222'), ('0'), ('333'); 351 SELECT HEX(str1) & BIN(88), BIN(str1) | HEX(88) FROM t1; 352 hex(str1) & bin(88) bin(str1) | hex(88) 353 279848 1101119 354 289824 11011134 355 24 58 356 328720 101001151 357 SELECT HEX(str1) & BIN(88) ^ BIN(str1) | HEX(100) FROM t1; 358 hex(str1) & bin(88) ^ bin(str1) | hex(100) 359 294987 360 288832 361 88 362 597 363 SELECT HEX(str1) >> BIN(88) << BIN(str1) >> HEX(100) FROM t1; 364 hex(str1) >> bin(88) << bin(str1) >> hex(100) 365 0 366 0 367 0 368 0 369 DROP TABLE t1; 370 select binary(3) & binary(4); 371 binary(3) & binary(4) 372 0 373 select binary(3) | binary(4); 374 binary(3) | binary(4) 375 7