github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_aggr_bitwise.test (about) 1 #NULL 2 3 SELECT bit_and(null), bit_or(null), bit_xor(null); 4 5 #EXTREME VALUE 6 7 CREATE TABLE t1 (a int, b int); 8 INSERT INTO t1 VALUES (1,null); 9 INSERT INTO t1 VALUES (1,null); 10 INSERT INTO t1 VALUES (2,null); 11 select a, BIT_AND(b), BIT_OR(b), BIT_XOR(b) from t1 group by a; 12 SELECT a, OCT(BIT_AND(b)), OCT(BIT_OR(b)), OCT(BIT_XOR(b)) FROM t1 GROUP BY a; 13 SELECT OCT(BIT_AND(b)), OCT(BIT_OR(b)), OCT(BIT_XOR(b)) FROM t1; 14 INSERT INTO t1 VALUES (3,123); 15 SELECT a, OCT(BIT_AND(b)), OCT(BIT_OR(b)), OCT(BIT_XOR(b)) FROM t1 GROUP BY a; 16 INSERT INTO t1 VALUES (2,124124), (3, 4951312); 17 SELECT a, OCT(BIT_AND(b)), OCT(BIT_OR(b)), OCT(BIT_XOR(b)) FROM t1 GROUP BY a; 18 INSERT INTO t1 VALUES (4,-4124124); 19 SELECT a, OCT(BIT_AND(b)), OCT(BIT_OR(b)), OCT(BIT_XOR(b)) FROM t1 GROUP BY a; 20 DROP TABLE t1; 21 22 23 #DATA TYPES 24 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)); 25 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"); 26 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"); 27 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"); 28 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"); 29 select bit_and(a) from t1; 30 select bit_and(b) from t1; 31 select bit_and(c) from t1; 32 select bit_and(d) from t1; 33 select bit_and(e) from t1; 34 35 36 select bit_and(f) from t1; 37 38 select bit_and(g) from t1; 39 40 select bit_and(h) from t1; 41 select bit_and(i) from t1; 42 select bit_and(k) from t1; 43 select bit_and(l) from t1; 44 -- @bvt:issue#3373 45 select bit_and(m) from t1; 46 select bit_and(n) from t1; 47 -- @bvt:issue 48 drop table t1; 49 50 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)); 51 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"); 52 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"); 53 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"); 54 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"); 55 select bit_or(a) from t1; 56 select bit_or(b) from t1; 57 select bit_or(c) from t1; 58 select bit_or(d) from t1; 59 select bit_or(e) from t1; 60 61 62 select bit_or(f) from t1; 63 64 select bit_or(g) from t1; 65 66 select bit_or(h) from t1; 67 select bit_or(i) from t1; 68 select bit_or(k) from t1; 69 select bit_or(l) from t1; 70 -- @bvt:issue#3373 71 select bit_or(m) from t1; 72 select bit_or(n) from t1; 73 -- @bvt:issue 74 drop table t1; 75 76 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)); 77 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"); 78 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"); 79 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"); 80 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"); 81 select bit_xor(a) from t1; 82 select bit_xor(b) from t1; 83 select bit_xor(c) from t1; 84 select bit_xor(d) from t1; 85 select bit_xor(e) from t1; 86 87 select bit_xor(f) from t1; 88 89 select bit_xor(g) from t1; 90 91 select bit_xor(h) from t1; 92 select bit_xor(i) from t1; 93 select bit_xor(k) from t1; 94 select bit_xor(l) from t1; 95 -- @bvt:issue#3373 96 select bit_xor(m) from t1; 97 select bit_xor(n) from t1; 98 -- @bvt:issue 99 drop table t1; 100 101 #0.5暂不支持time类型 102 #create table t1(a time) 103 #insert into t1 values("10:03:12"); 104 #select bit_and(a), BIT_OR(a),BIT_XOR(a) from t1; 105 #drop table t1; 106 107 #EXTREME VALUE, DISTINCT 108 109 select BIT_AND(99999999999999999.99999), BIT_OR(99999999999999999.99999), BIT_XOR(99999999999999999.99999); 110 select BIT_AND(999999999999999933193939.99999),BIT_OR(999999999999999933193939.99999),BIT_XOR(999999999999999933193939.99999); 111 select BIT_AND(9999999999999999999999999999999999.9999999999999),BIT_OR(9999999999999999999999999999999999.9999999999999),BIT_XOR(9999999999999999999999999999999999.9999999999999); 112 select BIT_AND(-99999999999999999.99999),BIT_OR(-99999999999999999.99999),BIT_XOR(-99999999999999999.99999); 113 select BIT_AND(-999999999999999933193939.99999),BIT_OR(-999999999999999933193939.99999),BIT_XOR(-999999999999999933193939.99999); 114 select BIT_AND(-9999999999999999999999999999999999.9999999999999),BIT_OR(-9999999999999999999999999999999999.9999999999999),BIT_XOR(-9999999999999999999999999999999999.9999999999999); 115 116 create table t1(a bigint); 117 select BIT_AND(a),BIT_OR(a), BIT_XOR(a) from t1; 118 insert into t1 values(null),(null),(null),(null); 119 select BIT_AND(a),BIT_OR(a), BIT_XOR(a) from t1; 120 insert into t1 values(12417249128419),(124124125124151),(5124125151415),(124125152651515); 121 select BIT_AND(a),BIT_OR(a), BIT_XOR(a) from t1; 122 drop table t1; 123 124 125 create table t1 ( a int not null default 1, big bigint ); 126 insert into t1 (big) values (-1),(1234567890167),(92233720368547),(18446744073709515); 127 select * from t1; 128 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; 129 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; 130 insert into t1 (big) values (184467440737615); 131 select * from t1; 132 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; 133 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; 134 drop table t1; 135 136 137 138 139 #HAVING,DISTINCT 140 CREATE TABLE t1 (Fld1 int(11) default NULL,Fld2 int(11) default NULL); 141 INSERT INTO t1 VALUES (1,10),(1,20),(2,NULL),(2,NULL),(3,50); 142 select Fld1, BIT_AND(Fld2),BIT_OR(Fld2), BIT_XOR(Fld2) as q from t1 group by Fld1 having q is not null; 143 select Fld1, BIT_AND(Fld2),BIT_OR(Fld2), BIT_XOR(Fld2) from t1 group by Fld1 having BIT_AND(Fld2) is not null; 144 select Fld1, BIT_AND(Fld2),BIT_OR(Fld2), BIT_XOR(Fld2) from t1 group by Fld1 having BIT_OR(Fld2) is not null; 145 select Fld1, BIT_AND(Fld2),BIT_OR(Fld2), BIT_XOR(Fld2) from t1 group by Fld1 having BIT_XOR(Fld2) is not null; 146 drop table t1; 147 148 149 150 #比较操作 151 SELECT BIT_AND(1)<BIT_AND(2), BIT_AND(1)>BIT_OR(3), BIT_AND(3)>BIT_XOR(5); 152 SELECT BIT_AND(1 - 1), BIT_OR(1 - (-1)), BIT_XOR(1 / 0);