|
I create a table with an unsigned column and insert a huge positive value into it:
CREATE OR REPLACE TABLE t1 (pk INT, a INT, b BIGINT UNSIGNED);
|
INSERT INTO t1 VALUES (1, 0, 1), (2, 0, 18446744073709551615);
|
Regular aggregate BIT_OR() works as expected:
CREATE OR REPLACE TABLE t2 AS
|
SELECT pk, a, bit_or(b) AS bit_or FROM t1 GROUP BY pk;
|
SHOW CREATE TABLE t2;
|
SELECT * FROM t1;
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`pk` int(11) DEFAULT NULL,
|
`a` int(11) DEFAULT NULL,
|
`bit_or` bigint(21) unsigned NOT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
+------+------+----------------------+
|
| pk | a | b |
|
+------+------+----------------------+
|
| 1 | 0 | 1 |
|
| 2 | 0 | 18446744073709551615 |
|
+------+------+----------------------+
|
Notice it created the column `bit_or` as BIGINT UNSIGNED as expected, and the huge value is returned back from SELECT.
So far so good.
Now I do the same using BIT_OR() as a window function:
CREATE OR REPLACE TABLE t2 AS
|
SELECT pk, a,
|
BIT_OR(b) OVER (PARTITION BY a ORDER BY pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS bit_or
|
FROM t1;
|
SHOW CREATE TABLE t2;
|
SELECT * FROM t2;
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`pk` int(11) DEFAULT NULL,
|
`a` int(11) DEFAULT NULL,
|
`bit_or` bigint(21) NOT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
|
+------+------+--------+
|
| pk | a | bit_or |
|
+------+------+--------+
|
| 1 | 0 | -1 |
|
| 2 | 0 | -1 |
|
+------+------+--------+
|
Notice, it created `bit_or` as a signed BIGINT and the SELECT query returns -1 instead of the expected value of 18446744073709551615.
|