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

UNION on signed and unsigned integer types returns incorrect results

Details

    Description

      Here is an example SQL script with wrong outputs when UNOIN of MCS computes with signed and unsigned integers. The results are all wrong.

      use mysql;
      SET default_storage_engine=ColumnStore;
      CREATE TABLE t1 (a SMALLINT);
      INSERT INTO t1 VALUES (CAST(0xFFFFFFFFFFFF8000 AS SIGNED)+2),(-1),(0),(1),(0x7FFF);
      CREATE TABLE t2 (a INT UNSIGNED);
      INSERT INTO t2 VALUES (0),(1),(0xFFFF-2);
      SELECT * FROM ((SELECT a FROM t1 UNION ALL SELECT a FROM t2) as tu) ORDER BY a;
      SELECT * FROM ((SELECT a FROM t2 UNION ALL SELECT a FROM t1) as tu) ORDER BY a;
      

      +-------+
      | a     |
      +-------+
      | 0     |
      | 0     |
      | 1     |
      | 1     |
      | 32767 |
      | 32770 |
      | 65533 |
      | 65535 |
      +-------+
      8 rows in set
      Time: 0.013s
       
      +-------+
      | a     |
      +-------+
      | 0     |
      | 0     |
      | 1     |
      | 1     |
      | 32767 |
      | 32770 |
      | 65533 |
      | 65535 |
      +-------+
      8 rows in set
      Time: 0.015s
      

      The correct output should be the same under the innodb engine, so we change the second line in the script above to `SET default_storage_engine=InnoDB;`:

      MariaDB [mysql]> SELECT * FROM ((SELECT a FROM t1 UNION ALL SELECT a FROM t2) as tu) ORDER BY a;
      +--------+
      | a      |
      +--------+
      | -32766 |
      |     -1 |
      |      0 |
      |      0 |
      |      1 |
      |      1 |
      |  32767 |
      |  65533 |
      +--------+
      8 rows in set (0.000 sec)
       
      MariaDB [mysql]> SELECT * FROM ((SELECT a FROM t2 UNION ALL SELECT a FROM t1) as tu) ORDER BY a;
      +--------+
      | a      |
      +--------+
      | -32766 |
      |     -1 |
      |      0 |
      |      0 |
      |      1 |
      |      1 |
      |  32767 |
      |  65533 |
      +--------+
      8 rows in set (0.001 sec)
      

      Attachments

        Issue Links

          Activity

            Jigao Luo Jigao Luo added a comment -

            Hi everyone.
            I am a student from GSOC this year. My task is to improve the performance UNION processing in MCS. I have tried to fix this bug. So please assign this issue to me. Thanks.

            I have detected this bug during my development and optimization of UNION in MCS.
            I think fixing this bug is an important task to me and the community:

            • With this bug, a common case UNION on integers produces always wrong results to the end users.
            • The performance improvement must be based on the correct result. Start correct, then improve.
            • The UNION of MCS is only equipped with a limited number of basic unit tests. If more unit tests were included, this bug could be located by a test similar to the script above.
            Jigao Luo Jigao Luo added a comment - Hi everyone. I am a student from GSOC this year. My task is to improve the performance UNION processing in MCS. I have tried to fix this bug. So please assign this issue to me. Thanks. I have detected this bug during my development and optimization of UNION in MCS. I think fixing this bug is an important task to me and the community: With this bug, a common case UNION on integers produces always wrong results to the end users. The performance improvement must be based on the correct result. Start correct, then improve. The UNION of MCS is only equipped with a limited number of basic unit tests. If more unit tests were included, this bug could be located by a test similar to the script above.

            For QA: Instructions for reproduction are in the ticket description.

            tntnatbry Gagan Goel (Inactive) added a comment - For QA: Instructions for reproduction are in the ticket description.

            Build verified:
            engine: 4d4e4ad30dd2ec494ea9d323e1fa7fa69e97243e
            server: 1916028f898cf672a8b79d2b585b1d74f8bcd7db
            buildNo: 6965

            Verified with the test case in the description.

            ColumnStore results now matching InnoDB results.

            dleeyh Daniel Lee (Inactive) added a comment - Build verified: engine: 4d4e4ad30dd2ec494ea9d323e1fa7fa69e97243e server: 1916028f898cf672a8b79d2b585b1d74f8bcd7db buildNo: 6965 Verified with the test case in the description. ColumnStore results now matching InnoDB results.

            People

              Jigao Luo Jigao Luo
              Jigao Luo Jigao Luo
              Gagan Goel Gagan Goel (Inactive)
              Daniel Lee Daniel Lee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.