github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_bit.result (about) 1 SELECT BIT_AND(0), BIT_OR(0), BIT_XOR(0); 2 bit_and(0) bit_or(0) bit_xor(0) 3 0 0 0 4 SELECT BIT_AND(1), BIT_OR(1), BIT_XOR(1); 5 bit_and(1) bit_or(1) bit_xor(1) 6 1 1 1 7 SELECT BIT_AND(-1), BIT_OR(-1), BIT_XOR(-1); 8 bit_and(-1) bit_or(-1) bit_xor(-1) 9 18446744073709551615 18446744073709551615 18446744073709551615 10 SELECT BIT_AND(ABS(-1)), BIT_OR(ABS(-1)), BIT_XOR(ABS(-1)); 11 bit_and(abs(-1)) bit_or(abs(-1)) bit_xor(abs(-1)) 12 1 1 1 13 SELECT BIT_AND(1 + 1), BIT_OR(1 + 1), BIT_XOR(1 + 1); 14 bit_and(1 + 1) bit_or(1 + 1) bit_xor(1 + 1) 15 2 2 2 16 SELECT BIT_AND(COS(0)), BIT_OR(COS(0)), BIT_XOR(COS(0)); 17 bit_and(cos(0)) bit_or(cos(0)) bit_xor(cos(0)) 18 1 1 1 19 DROP TABLE IF EXISTS t; 20 CREATE TABLE t( 21 tiny TINYINT NOT NULL, 22 small SMALLINT NOT NULL, 23 int_t INT NOT NULL, 24 big BIGINT NOT NULL 25 ); 26 INSERT INTO t VALUES (-128, -32768, -2147483648, -9223372036854775808); 27 INSERT INTO t VALUES (127, 32767, 2147483647, 9223372036854775807); 28 SELECT BIT_AND(tiny), BIT_OR(tiny), BIT_XOR(tiny) FROM t; 29 bit_and(tiny) bit_or(tiny) bit_xor(tiny) 30 0 18446744073709551615 18446744073709551615 31 SELECT BIT_AND(small), BIT_OR(small), BIT_XOR(small) FROM t; 32 bit_and(small) bit_or(small) bit_xor(small) 33 0 18446744073709551615 18446744073709551615 34 SELECT BIT_AND(int_t), BIT_OR(int_t), BIT_XOR(int_t) FROM t; 35 bit_and(int_t) bit_or(int_t) bit_xor(int_t) 36 0 18446744073709551615 18446744073709551615 37 SELECT BIT_AND(big), BIT_OR(big), BIT_XOR(big) FROM t; 38 bit_and(big) bit_or(big) bit_xor(big) 39 0 18446744073709551615 18446744073709551615 40 DROP TABLE IF EXISTS t; 41 CREATE TABLE t( 42 tiny TINYINT UNSIGNED NOT NULL, 43 small SMALLINT UNSIGNED NOT NULL, 44 int_t INT UNSIGNED NOT NULL, 45 big BIGINT UNSIGNED NOT NULL 46 ); 47 INSERT INTO t VALUES (255, 65535, 65535, 18446744073709551615); 48 SELECT BIT_AND(tiny), BIT_OR(tiny), BIT_XOR(tiny) FROM t; 49 bit_and(tiny) bit_or(tiny) bit_xor(tiny) 50 255 255 255 51 SELECT BIT_AND(small), BIT_OR(small), BIT_XOR(small) FROM t; 52 bit_and(small) bit_or(small) bit_xor(small) 53 65535 65535 65535 54 SELECT BIT_AND(int_t), BIT_OR(int_t), BIT_XOR(int_t) FROM t; 55 bit_and(int_t) bit_or(int_t) bit_xor(int_t) 56 65535 65535 65535 57 SELECT BIT_AND(big), BIT_OR(big), BIT_XOR(big) FROM t; 58 bit_and(big) bit_or(big) bit_xor(big) 59 18446744073709551615 18446744073709551615 18446744073709551615 60 DROP TABLE IF EXISTS t; 61 CREATE TABLE t( 62 tiny TINYINT NOT NULL, 63 small SMALLINT NOT NULL, 64 int_t INT NOT NULL, 65 big BIGINT NOT NULL 66 ); 67 INSERT INTO t VALUES (0, 1, -1, 1-0); 68 INSERT INTO t VALUES (1, 9, 66666, TRUE); 69 SELECT BIT_AND(tiny + small), BIT_AND(int_t + big) FROM t; 70 bit_and(tiny + small) bit_and(int_t + big) 71 0 0 72 SELECT BIT_AND(tiny + SIN(0)), BIT_XOR(int_t + TAN(0)) FROM t; 73 bit_and(tiny + sin(0)) bit_xor(int_t + tan(0)) 74 0 18446744073709484949 75 SELECT BIT_OR(tiny + small), BIT_XOR(int_t + big) FROM t; 76 bit_or(tiny + small) bit_xor(int_t + big) 77 11 66667 78 SELECT BIT_XOR(tiny + SIN(0)), BIT_OR(int_t + big) FROM t; 79 bit_xor(tiny + sin(0)) bit_or(int_t + big) 80 1 66667 81 SELECT BIT_AND(tiny + 1), BIT_OR(small - 1), BIT_XOR(int_t / 0) FROM t; 82 [unknown result because it is related to issue#5638] 83 DROP TABLE IF EXISTS t; 84 DROP TABLE IF EXISTS t1; 85 CREATE TABLE t( 86 id INT, 87 score FLOAT, 88 PRIMARY KEY (id) 89 ); 90 CREATE TABLE t1( 91 id INT, 92 salary DOUBLE, 93 PRIMARY KEY (id) 94 ); 95 INSERT INTO t VALUES (1, 67.5), (2, 87.5), (3, 72.5), (4, 90.1), (5, 81.0); 96 INSERT INTO t1 VALUES (1, 1120.2), (2, 4320), (3, 9078), (4, 3071); 97 SELECT BIT_AND(score) FROM t JOIN t1 ON t.id = t1.id WHERE t.id > 1; 98 bit_and(score) 99 64 100 SELECT BIT_AND(salary) FROM t LEFT JOIN t1 ON t.id = t1.id; 101 bit_and(salary) 102 96 103 SELECT BIT_OR(salary) FROM t LEFT JOIN t1 ON t.id = t1.id WHERE t.id BETWEEN 2 AND 5; 104 bit_or(salary) 105 15359 106 SELECT BIT_OR(score) FROM t RIGHT JOIN t1 ON t.id = t1.id; 107 bit_or(score) 108 95 109 SELECT BIT_XOR(salary) FROM t RIGHT JOIN t1 ON t.id = t1.id; 110 bit_xor(salary) 111 15369 112 DROP TABLE IF EXISTS t; 113 CREATE TABLE t( 114 id INT, 115 str1 VARCHAR(20), 116 PRIMARY KEY (id) 117 ); 118 INSERT INTO t VALUES (1,'123'), (2,'234'), (3,'345'), (4,'456'); 119 SELECT BIT_OR(CAST(str1 AS FLOAT)) FROM t; 120 bit_or(cast(str1 as float)) 121 507 122 SELECT BIT_XOR(CAST(str1 AS DOUBLE)) FROM t; 123 bit_xor(cast(str1 as double)) 124 0 125 DROP TABLE IF EXISTS t; 126 DROP TABLE IF EXISTS t1; 127 CREATE TABLE t( 128 id INT, 129 str1 VARCHAR(50), 130 str2 CHAR(50), 131 PRIMARY KEY (id) 132 ); 133 CREATE TABLE t1( 134 id INT, 135 str1 VARCHAR(50), 136 str2 CHAR(50), 137 PRIMARY KEY (id) 138 ); 139 INSERT INTO t VALUES (1,'12.3','9.00200001'), (2,'0','1'), (3,'0.000000000000111101010101','189'), (4,'456','1.001'); 140 INSERT INTO t1 VALUES (1,'00000.01','3.001'), (2,'-1','99999999999999999'), (3,'1.0111','0.00001'), (4,'1.01','000.001'); 141 SELECT BIT_AND(CAST(t.str1 AS FLOAT)), BIT_AND(CAST(t1.str2 AS DOUBLE)) FROM t,t1; 142 bit_and(cast(t.str1 as float)) bit_and(cast(t1.str2 as double)) 143 0 0 144 SELECT BIT_AND(CAST(t.str2 AS FLOAT)), BIT_OR(CAST(t1.str1 AS DOUBLE)) FROM t,t1; 145 bit_and(cast(t.str2 as float)) bit_or(cast(t1.str1 as double)) 146 1 18446744073709551615 147 SELECT BIT_XOR(CAST(t.str1 AS FLOAT)), BIT_AND(CAST(t1.str2 AS FLOAT)) FROM t JOIN t1 ON t.id = t1.id; 148 bit_xor(cast(t.str1 as float)) bit_and(cast(t1.str2 as float)) 149 452 0 150 SELECT BIT_XOR(CAST(t.str1 AS FLOAT)), BIT_AND(CAST(t.str2 AS FLOAT)), BIT_OR(CAST(t1.str1 AS FLOAT)) FROM t JOIN t1 ON t.id = t1.id; 151 bit_xor(cast(t.str1 as float)) bit_and(cast(t.str2 as float)) bit_or(cast(t1.str1 as float)) 152 452 1 18446744073709551615 153 SELECT BIT_XOR(CAST(t.str1 AS DOUBLE)), BIT_XOR(CAST(t1.str2 AS FLOAT)) FROM t RIGHT JOIN t1 ON t.id = t1.id; 154 bit_xor(cast(t.str1 as double)) bit_xor(cast(t1.str2 as float)) 155 452 99999998430674947 156 DROP TABLE IF EXISTS t; 157 CREATE TABLE t( 158 id INT, 159 d1 DECIMAL(28,20), 160 PRIMARY KEY(id) 161 ); 162 INSERT INTO t VALUES (1,12.300101010100), (2,34.557978974325555),(3,56.),(4,0.000043524389789999); 163 SELECT * FROM t; 164 id d1 165 1 12.30010101010000000000 166 2 34.55797897432555500000 167 3 56.00000000000000000000 168 4 0.00004352438978999900 169 SELECT BIT_AND(d1) FROM t; 170 bit_and(d1) 171 0 172 DROP TABLE IF EXISTS t; 173 CREATE TABLE t( 174 id INT, 175 d1 DECIMAL, 176 PRIMARY KEY(id) 177 ); 178 INSERT INTO t VALUES (1,12.300101010100), (2,34.557978974325555),(3,56.),(4,0.000043524389789999); 179 SELECT * FROM t; 180 id d1 181 1 12 182 2 35 183 3 56 184 4 0 185 SELECT BIT_AND(d1), BIT_OR(d1), BIT_XOR(d1) FROM t; 186 bit_and(d1) bit_or(d1) bit_xor(d1) 187 0 63 23