Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3(EOL), 10.4(EOL), 10.5
-
None
Description
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.
Attachments
Issue Links
- blocks
-
MDEV-4912 Data type plugin API version 1
-
- Closed
-