Syntax
BIT_AND(expr)
Purpose
Returns the bitwise AND of all bits in expr.
The result type depends on whether the function parameters are evaluated as binary strings or as numbers. Binary string evaluation occurs when the parameter value is a binary string and is not a hexadecimal literal, bit literal, or NULL literal. Otherwise, numeric computation occurs, and the parameter value is converted to an unsigned 64-bit integer when necessary.
If no rows match the condition, BIT_AND() returns a neutral value with the same length as the parameter value (all bits set to 1).
NULL values do not affect the result unless all values are NULL. In that case, the result is a neutral value with the same length as the parameter value.
Examples
CREATE TABLE product_information(supplier_id INT, product_id INT,list_price numeric, min_price numeric);
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '1659', '45', NULL);
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '1770', NULL, '70');
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '2370', '305', '247');
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '2380', '750', '731');
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '3255', NULL, NULL);
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '1659', '35', NULL);
obclient> SELECT * FROM product_information;
+-------------+------------+------------+-----------+
| supplier_id | product_id | list_price | min_price |
+-------------+------------+------------+-----------+
| 102050 | 1659 | 45 | NULL |
| 102050 | 1770 | NULL | 70 |
| 102050 | 2370 | 305 | 247 |
| 102050 | 2380 | 750 | 731 |
| 102050 | 3255 | NULL | NULL |
| 102050 | 2380 | 750 | 731 |
| 102050 | 1659 | 35 | NULL |
+-------------+------------+------------+-----------+
7 rows in set
obclient> SELECT product_id, BIT_AND(list_price) BITS FROM product_information GROUP BY product_id;
+------------+----------------------+
| product_id | BITS |
+------------+----------------------+
| 1659 | 33 |
| 1770 | 18446744073709551615 |
| 2370 | 305 |
| 2380 | 750 |
| 3255 | 18446744073709551615 |
+------------+----------------------+
5 rows in set