github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_aggr_bitwise.result (about) 1 SELECT bit_and(null), bit_or(null), bit_xor(null); 2 bit_and(null) bit_or(null) bit_xor(null) 3 null null null 4 CREATE TABLE t1 (a int, b int); 5 INSERT INTO t1 VALUES (1,null); 6 INSERT INTO t1 VALUES (1,null); 7 INSERT INTO t1 VALUES (2,null); 8 select a, BIT_AND(b), BIT_OR(b), BIT_XOR(b) from t1 group by a; 9 a bit_and(b) bit_or(b) bit_xor(b) 10 1 null null null 11 2 null null null 12 SELECT a, OCT(BIT_AND(b)), OCT(BIT_OR(b)), OCT(BIT_XOR(b)) FROM t1 GROUP BY a; 13 a oct(bit_and(b)) oct(bit_or(b)) oct(bit_xor(b)) 14 1 null null null 15 2 null null null 16 SELECT OCT(BIT_AND(b)), OCT(BIT_OR(b)), OCT(BIT_XOR(b)) FROM t1; 17 oct(bit_and(b)) oct(bit_or(b)) oct(bit_xor(b)) 18 null null null 19 INSERT INTO t1 VALUES (3,123); 20 SELECT a, OCT(BIT_AND(b)), OCT(BIT_OR(b)), OCT(BIT_XOR(b)) FROM t1 GROUP BY a; 21 a oct(bit_and(b)) oct(bit_or(b)) oct(bit_xor(b)) 22 1 null null null 23 2 null null null 24 3 173 173 173 25 INSERT INTO t1 VALUES (2,124124), (3, 4951312); 26 SELECT a, OCT(BIT_AND(b)), OCT(BIT_OR(b)), OCT(BIT_XOR(b)) FROM t1 GROUP BY a; 27 a oct(bit_and(b)) oct(bit_or(b)) oct(bit_xor(b)) 28 1 null null null 29 2 362334 362334 362334 30 3 20 22706573 22706553 31 INSERT INTO t1 VALUES (4,-4124124); 32 SELECT a, OCT(BIT_AND(b)), OCT(BIT_OR(b)), OCT(BIT_XOR(b)) FROM t1 GROUP BY a; 33 a oct(bit_and(b)) oct(bit_or(b)) oct(bit_xor(b)) 34 1 null null null 35 2 362334 362334 362334 36 3 20 22706573 22706553 37 4 1777777777777760211044 1777777777777760211044 1777777777777760211044 38 DROP TABLE t1; 39 create table t1(a tinyint, b SMALLINT, c bigint, d INT, e BIGINT, f FLOAT, g DOUBLE, h decimal(38,19), i DATE, k datetime, l TIMESTAMP, m char(255), n varchar(255)); 40 insert into t1 values(1, 1, 2, 43, 5, 35.5, 31.133, 14.314, "2012-03-10", "2012-03-12 10:03:12", "2022-03-12 13:03:12", "ab23c", "d5cf"); 41 insert into t1 values(71, 1, 2, 34, 5, 5.5, 341.13, 15.314, "2012-03-22", "2013-03-12 10:03:12", "2032-03-12 13:04:12", "abr23c", "3dcf"); 42 insert into t1 values(1, 1, 21, 4, 54, 53.5, 431.13, 14.394, "2011-03-12", "2015-03-12 10:03:12", "2002-03-12 13:03:12", "afbc", "dct5f"); 43 insert into t1 values(1, 71, 2, 34, 5, 5.5, 31.313, 124.314, "2012-01-12", "2019-03-12 10:03:12", "2013-03-12 13:03:12", "3abd1c", "dcvf"); 44 select bit_and(a) from t1; 45 bit_and(a) 46 1 47 select bit_and(b) from t1; 48 bit_and(b) 49 1 50 select bit_and(c) from t1; 51 bit_and(c) 52 0 53 select bit_and(d) from t1; 54 bit_and(d) 55 0 56 select bit_and(e) from t1; 57 bit_and(e) 58 4 59 select bit_and(f) from t1; 60 bit_and(f) 61 1 62 select bit_and(g) from t1; 63 bit_and(g) 64 5 65 select bit_and(h) from t1; 66 bit_and(h) 67 12 68 select bit_and(i) from t1; 69 invalid argument aggregate function bit_and, bad value [DATE] 70 select bit_and(k) from t1; 71 invalid argument aggregate function bit_and, bad value [DATETIME] 72 select bit_and(l) from t1; 73 invalid argument aggregate function bit_and, bad value [TIMESTAMP] 74 select bit_and(m) from t1; 75 bit_and(m) 76 0 77 select bit_and(n) from t1; 78 bit_and(n) 79 0 80 drop table t1; 81 create table t1(a tinyint, b SMALLINT, c bigint, d INT, e BIGINT, f FLOAT, g DOUBLE, h decimal(38,19), i DATE, k datetime, l TIMESTAMP, m char(255), n varchar(255)); 82 insert into t1 values(1, 1, 2, 43, 5, 35.5, 31.133, 14.314, "2012-03-10", "2012-03-12 10:03:12", "2022-03-12 13:03:12", "ab23c", "d5cf"); 83 insert into t1 values(71, 1, 2, 34, 5, 5.5, 341.13, 15.314, "2012-03-22", "2013-03-12 10:03:12", "2032-03-12 13:04:12", "abr23c", "3dcf"); 84 insert into t1 values(1, 1, 21, 4, 54, 53.5, 431.13, 14.394, "2011-03-12", "2015-03-12 10:03:12", "2002-03-12 13:03:12", "afbc", "dct5f"); 85 insert into t1 values(1, 71, 2, 34, 5, 5.5, 31.313, 124.314, "2012-01-12", "2019-03-12 10:03:12", "2013-03-12 13:03:12", "3abd1c", "dcvf"); 86 select bit_or(a) from t1; 87 bit_or(a) 88 71 89 select bit_or(b) from t1; 90 bit_or(b) 91 71 92 select bit_or(c) from t1; 93 bit_or(c) 94 23 95 select bit_or(d) from t1; 96 bit_or(d) 97 47 98 select bit_or(e) from t1; 99 bit_or(e) 100 55 101 select bit_or(f) from t1; 102 bit_or(f) 103 55 104 select bit_or(g) from t1; 105 bit_or(g) 106 511 107 select bit_or(h) from t1; 108 bit_or(h) 109 127 110 select bit_or(i) from t1; 111 invalid argument aggregate function bit_or, bad value [DATE] 112 select bit_or(k) from t1; 113 invalid argument aggregate function bit_or, bad value [DATETIME] 114 select bit_or(l) from t1; 115 invalid argument aggregate function bit_or, bad value [TIMESTAMP] 116 select bit_or(m) from t1; 117 bit_or(m) 118 3 119 select bit_or(n) from t1; 120 bit_or(n) 121 3 122 drop table t1; 123 create table t1(a tinyint, b SMALLINT, c bigint, d INT, e BIGINT, f FLOAT, g DOUBLE, h decimal(38,19), i DATE, k datetime, l TIMESTAMP, m char(255), n varchar(255)); 124 insert into t1 values(1, 1, 2, 43, 5, 35.5, 31.133, 14.314, "2012-03-10", "2012-03-12 10:03:12", "2022-03-12 13:03:12", "ab23c", "d5cf"); 125 insert into t1 values(71, 1, 2, 34, 5, 5.5, 341.13, 15.314, "2012-03-22", "2013-03-12 10:03:12", "2032-03-12 13:04:12", "abr23c", "3dcf"); 126 insert into t1 values(1, 1, 21, 4, 54, 53.5, 431.13, 14.394, "2011-03-12", "2015-03-12 10:03:12", "2002-03-12 13:03:12", "afbc", "dct5f"); 127 insert into t1 values(1, 71, 2, 34, 5, 5.5, 31.313, 124.314, "2012-01-12", "2019-03-12 10:03:12", "2013-03-12 13:03:12", "3abd1c", "dcvf"); 128 select bit_xor(a) from t1; 129 bit_xor(a) 130 70 131 select bit_xor(b) from t1; 132 bit_xor(b) 133 70 134 select bit_xor(c) from t1; 135 bit_xor(c) 136 23 137 select bit_xor(d) from t1; 138 bit_xor(d) 139 47 140 select bit_xor(e) from t1; 141 bit_xor(e) 142 51 143 select bit_xor(f) from t1; 144 bit_xor(f) 145 22 146 select bit_xor(g) from t1; 147 bit_xor(g) 148 250 149 select bit_xor(h) from t1; 150 bit_xor(h) 151 115 152 select bit_xor(i) from t1; 153 invalid argument aggregate function bit_xor, bad value [DATE] 154 select bit_xor(k) from t1; 155 invalid argument aggregate function bit_xor, bad value [DATETIME] 156 select bit_xor(l) from t1; 157 invalid argument aggregate function bit_xor, bad value [TIMESTAMP] 158 select bit_xor(m) from t1; 159 bit_xor(m) 160 3 161 select bit_xor(n) from t1; 162 bit_xor(n) 163 3 164 drop table t1; 165 select BIT_AND(99999999999999999.99999), BIT_OR(99999999999999999.99999), BIT_XOR(99999999999999999.99999); 166 bit_and(99999999999999999.99999) bit_or(99999999999999999.99999) bit_xor(99999999999999999.99999) 167 100000000000000000 100000000000000000 100000000000000000 168 select BIT_AND(999999999999999933193939.99999),BIT_OR(999999999999999933193939.99999),BIT_XOR(999999999999999933193939.99999); 169 bit_and(999999999999999933193939.99999) bit_or(999999999999999933193939.99999) bit_xor(999999999999999933193939.99999) 170 9223372036854775807 9223372036854775807 9223372036854775807 171 select BIT_AND(9999999999999999999999999999999999.9999999999999),BIT_OR(9999999999999999999999999999999999.9999999999999),BIT_XOR(9999999999999999999999999999999999.9999999999999); 172 bit_and(9999999999999999999999999999999999.9999999999999) bit_or(9999999999999999999999999999999999.9999999999999) bit_xor(9999999999999999999999999999999999.9999999999999) 173 9223372036854775807 9223372036854775807 9223372036854775807 174 select BIT_AND(-99999999999999999.99999),BIT_OR(-99999999999999999.99999),BIT_XOR(-99999999999999999.99999); 175 bit_and(-99999999999999999.99999) bit_or(-99999999999999999.99999) bit_xor(-99999999999999999.99999) 176 18346744073709551616 18346744073709551616 18346744073709551616 177 select BIT_AND(-999999999999999933193939.99999),BIT_OR(-999999999999999933193939.99999),BIT_XOR(-999999999999999933193939.99999); 178 bit_and(-999999999999999933193939.99999) bit_or(-999999999999999933193939.99999) bit_xor(-999999999999999933193939.99999) 179 9223372036854775808 9223372036854775808 9223372036854775808 180 select BIT_AND(-9999999999999999999999999999999999.9999999999999),BIT_OR(-9999999999999999999999999999999999.9999999999999),BIT_XOR(-9999999999999999999999999999999999.9999999999999); 181 bit_and(-9999999999999999999999999999999999.9999999999999) bit_or(-9999999999999999999999999999999999.9999999999999) bit_xor(-9999999999999999999999999999999999.9999999999999) 182 9223372036854775808 9223372036854775808 9223372036854775808 183 create table t1(a bigint); 184 select BIT_AND(a),BIT_OR(a), BIT_XOR(a) from t1; 185 bit_and(a) bit_or(a) bit_xor(a) 186 null null null 187 insert into t1 values(null),(null),(null),(null); 188 select BIT_AND(a),BIT_OR(a), BIT_XOR(a) from t1; 189 bit_and(a) bit_or(a) bit_xor(a) 190 null null null 191 insert into t1 values(12417249128419),(124124125124151),(5124125151415),(124125152651515); 192 select BIT_AND(a),BIT_OR(a), BIT_XOR(a) from t1; 193 bit_and(a) bit_or(a) bit_xor(a) 194 2097187 140668768616447 17479809270168 195 drop table t1; 196 create table t1 ( a int not null default 1, big bigint ); 197 insert into t1 (big) values (-1),(1234567890167),(92233720368547),(18446744073709515); 198 select * from t1; 199 a big 200 1 -1 201 1 1234567890167 202 1 92233720368547 203 1 18446744073709515 204 select distinct BIT_AND(big),BIT_OR(big), BIT_XOR(big),BIT_AND(big)-1, BIT_OR(big)-1, BIT_XOR(big)-1 from t1; 205 Data truncation: data out of range: data type int64, value '18446744073709551615' 206 select BIT_AND(big),BIT_OR(big), BIT_XOR(big),BIT_AND(big)-1, BIT_OR(big)-1, BIT_XOR(big)-1 from t1 group by a; 207 Data truncation: data out of range: data type int64, value '18446744073709551615' 208 insert into t1 (big) values (184467440737615); 209 select * from t1; 210 a big 211 1 -1 212 1 1234567890167 213 1 92233720368547 214 1 18446744073709515 215 1 184467440737615 216 select distinct BIT_AND(big),BIT_OR(big), BIT_XOR(big),BIT_AND(big)-1, BIT_OR(big)-1, BIT_XOR(big)-1 from t1; 217 Data truncation: data out of range: data type int64, value '18446744073709551615' 218 select BIT_AND(big),BIT_OR(big), BIT_XOR(big),BIT_AND(big)-1, BIT_OR(big)-1, BIT_XOR(big)-1 from t1 group by a; 219 Data truncation: data out of range: data type int64, value '18446744073709551615' 220 drop table t1; 221 CREATE TABLE t1 (Fld1 int(11) default NULL,Fld2 int(11) default NULL); 222 INSERT INTO t1 VALUES (1,10),(1,20),(2,NULL),(2,NULL),(3,50); 223 select Fld1, BIT_AND(Fld2),BIT_OR(Fld2), BIT_XOR(Fld2) as q from t1 group by Fld1 having q is not null; 224 fld1 bit_and(fld2) bit_or(fld2) q 225 1 0 30 30 226 3 50 50 50 227 select Fld1, BIT_AND(Fld2),BIT_OR(Fld2), BIT_XOR(Fld2) from t1 group by Fld1 having BIT_AND(Fld2) is not null; 228 fld1 bit_and(fld2) bit_or(fld2) bit_xor(fld2) 229 1 0 30 30 230 3 50 50 50 231 select Fld1, BIT_AND(Fld2),BIT_OR(Fld2), BIT_XOR(Fld2) from t1 group by Fld1 having BIT_OR(Fld2) is not null; 232 fld1 bit_and(fld2) bit_or(fld2) bit_xor(fld2) 233 1 0 30 30 234 3 50 50 50 235 select Fld1, BIT_AND(Fld2),BIT_OR(Fld2), BIT_XOR(Fld2) from t1 group by Fld1 having BIT_XOR(Fld2) is not null; 236 fld1 bit_and(fld2) bit_or(fld2) bit_xor(fld2) 237 1 0 30 30 238 3 50 50 50 239 drop table t1; 240 SELECT BIT_AND(1)<BIT_AND(2), BIT_AND(1)>BIT_OR(3), BIT_AND(3)>BIT_XOR(5); 241 BIT_AND(1)<BIT_AND(2) BIT_AND(1)>BIT_OR(3) BIT_AND(3)>BIT_XOR(5) 242 true false false 243 SELECT BIT_AND(1 - 1), BIT_OR(1 - (-1)), BIT_XOR(1 / 0); 244 Data truncation: division by zero 245 drop table if exists t1; 246 create table t1(a varbinary(3)); 247 insert into t1 values(3), (4), (9), (7), (6); 248 select bit_and(a), bit_or(a), bit_xor(a) from t1; 249 bit_and(a) bit_or(a) bit_xor(a) 250 0 ? ? 251 insert into t1 values(33); 252 select bit_and(a) from t1; 253 internal error: Binary operands of bitwise operators must be of equal length