Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20351

Window function BIT_OR() OVER (..) return a wrong data type

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.3, 10.4, 10.5
    • Fix Version/s: 10.3.18, 10.4.8
    • Component/s: Data types
    • Labels:
      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

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: