Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4700

Wrong result of a UNION for INT and INT UNSIGNED

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • 6.1.1
    • PrimProc
    • None

    Description

      This problem was found by tntnatbry while working on MCOL-4612 and MCOL-4613.

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT, b INT UNSIGNED) ENGINE=ColumnStore;
      INSERT INTO t1 VALUES (-1,1);
      SELECT * FROM (SELECT a FROM t1 UNION SELECT b FROM t1) tu;
      

      +------------+
      | a          |
      +------------+
      | 4294967295 |
      |          1 |
      +------------+
      

      Looks wrong. The expected result would be:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT, b INT UNSIGNED) ENGINE=InnoDB;
      INSERT INTO t1 VALUES (-1,1);
      SELECT * FROM (SELECT a FROM t1 UNION SELECT b FROM t1) tu;
      {code:sql}
      +------+
      | a    |
      +------+
      |   -1 |
      |    1 |
      +------+
      

      Attachments

        Issue Links

          Activity

            David.Hall David Hall (Inactive) added a comment - - edited

            The UNION code attempts to figure a common data type and translate the data to that for comparison. In this case, the common data type is UNSIGNED, so the result is unsigned. Any result column can only have one data type. Our code will not support a different type for different rows. If we choose INT as the common type, the result would be incorrect for a different data set.

            A potential fix is to choose a larger data type like BIGINT to hold the result. This would be large enough to handle the largest UNSIGNED and still handle negative numbers. It becomes a little more problematic if one of the types is UNSIGNED BIGINT. Perhaps we could do something with int128_t.

            David.Hall David Hall (Inactive) added a comment - - edited The UNION code attempts to figure a common data type and translate the data to that for comparison. In this case, the common data type is UNSIGNED, so the result is unsigned. Any result column can only have one data type. Our code will not support a different type for different rows. If we choose INT as the common type, the result would be incorrect for a different data set. A potential fix is to choose a larger data type like BIGINT to hold the result. This would be large enough to handle the largest UNSIGNED and still handle negative numbers. It becomes a little more problematic if one of the types is UNSIGNED BIGINT. Perhaps we could do something with int128_t.
            bar Alexander Barkov added a comment - - edited

            MariaDB chooses:

            • DECIMAL(11,0) to mix INT andd INT UNSIGNED.
            • DECIMAL(21,0) to mix BIGINT and BIGINT UNSIGNED.

            I think CS should choose the same data types.

            bar Alexander Barkov added a comment - - edited MariaDB chooses: DECIMAL(11,0) to mix INT andd INT UNSIGNED. DECIMAL(21,0) to mix BIGINT and BIGINT UNSIGNED. I think CS should choose the same data types.

            Build verified: 6.1.1 ( Drone #2585)

            Reproduced the issue in build 2584 and verified the fix in build 2585.

            MariaDB [mytest]> DROP TABLE IF EXISTS t1;
            Query OK, 0 rows affected, 1 warning (0.116 sec)

            MariaDB [mytest]> CREATE TABLE t1 (a INT, b INT UNSIGNED) ENGINE=ColumnStore;
            Query OK, 0 rows affected (0.637 sec)

            MariaDB [mytest]> INSERT INTO t1 VALUES (-1,1);
            Query OK, 1 row affected (0.317 sec)

            MariaDB [mytest]> SELECT * FROM (SELECT a FROM t1 UNION SELECT b FROM t1) tu;
            ------

            a

            ------

            1
            -1

            ------
            2 rows in set (0.070 sec)

            dleeyh Daniel Lee (Inactive) added a comment - Build verified: 6.1.1 ( Drone #2585) Reproduced the issue in build 2584 and verified the fix in build 2585. MariaDB [mytest] > DROP TABLE IF EXISTS t1; Query OK, 0 rows affected, 1 warning (0.116 sec) MariaDB [mytest] > CREATE TABLE t1 (a INT, b INT UNSIGNED) ENGINE=ColumnStore; Query OK, 0 rows affected (0.637 sec) MariaDB [mytest] > INSERT INTO t1 VALUES (-1,1); Query OK, 1 row affected (0.317 sec) MariaDB [mytest] > SELECT * FROM (SELECT a FROM t1 UNION SELECT b FROM t1) tu; ------ a ------ 1 -1 ------ 2 rows in set (0.070 sec)

            People

              dleeyh Daniel Lee (Inactive)
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.