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

UNION converts INT to BIGINT

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1, 10.2, 10.3
    • 10.3.1
    • OTHER

    Description

      This script creates columns of int(1) and int(2) data types:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 AS SELECT
        1,
        -1,
        COALESCE(1,1),
        COALESCE(-1,-1),
        COALESCE(1,-1),
        COALESCE(-1,1);
      SHOW CREATE TABLE t1;
      

      +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                                                                                             |
      +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `1` int(1) NOT NULL,
        `-1` int(2) NOT NULL,
        `COALESCE(1,1)` int(1) NOT NULL,
        `COALESCE(-1,-1)` int(2) NOT NULL,
        `COALESCE(1,-1)` int(2) NOT NULL,
        `COALESCE(-1,1)` int(2) NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

      int is preserved for literals.
      int is also preserved when data type aggregation takes place in hybrid function, such as COALESCE.

      However, int is not preserved when aggregation for UNION happens:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 AS SELECT 1 AS c1,1 AS c2,-1 AS c3,-1 AS c4 UNION SELECT 1,-1,1,-1;
      SHOW CREATE TABLE t1;
      

      +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                                                      |
      +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `c1` bigint(20) NOT NULL DEFAULT 0,
        `c2` bigint(20) NOT NULL DEFAULT 0,
        `c3` bigint(20) NOT NULL DEFAULT 0,
        `c4` bigint(20) NOT NULL DEFAULT 0
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

      int was not preserved. It created bigint columns instead, which looks too excessive.

      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.