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

UNION converts INT to BIGINT

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1, 10.2, 10.3
    • Fix Version/s: 10.3.1
    • Component/s: OTHER
    • Labels:

      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

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

                Dates

                Created:
                Updated:
                Resolved: