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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3, 10.4, 10.5
    • 10.3.18, 10.4.8
    • Data types
    • 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

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.