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

UNION between fixed length double columns does not always preserve scale

    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

      When two double columns with fixed scale are mixed for hybrid functions, e.g. COALESCE, the fixed length nature is always preserved:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a FLOAT(20,4), b FLOAT(20,3), c FLOAT(20,4));
      INSERT INTO t1 VALUES (1111,2222,3333);
      DROP TABLE IF EXISTS t2;
      CREATE TABLE t2 SELECT
        COALESCE(a,8888),
        COALESCE(8888,a),
        COALESCE(a,a),
        COALESCE(a,b),
        COALESCE(a,c),
        COALESCE(b,b),
        COALESCE(b,a),
        COALESCE(b,c),
        COALESCE(c,a),
        COALESCE(c,b),
        COALESCE(c,c)
      FROM t1\G
      SHOW CREATE TABLE t2;
      

      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `COALESCE(a,8888)` double(20,4) DEFAULT NULL,
        `COALESCE(8888,a)` double(20,4) DEFAULT NULL,
        `COALESCE(a,a)` double(20,4) DEFAULT NULL,
        `COALESCE(a,b)` double(21,4) DEFAULT NULL,
        `COALESCE(a,c)` double(20,4) DEFAULT NULL,
        `COALESCE(b,b)` double(20,3) DEFAULT NULL,
        `COALESCE(b,a)` double(21,4) DEFAULT NULL,
        `COALESCE(b,c)` double(21,4) DEFAULT NULL,
        `COALESCE(c,a)` double(20,4) DEFAULT NULL,
        `COALESCE(c,b)` double(21,4) DEFAULT NULL,
        `COALESCE(c,c)` double(20,4) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

      Fixed length is also preserved in UNION in case if the columns have exactly the length and scale:

      CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1;
      SHOW CREATE TABLE t2;
      

      +-------+-------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                              |
      +-------+-------------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `a` float(20,4) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-------------------------------------------------------------------------------------------+
      

      CREATE OR REPLACE TABLE t2 SELECT a FROM t1 UNION SELECT c FROM t1;
      SHOW CREATE TABLE t2;
      

      +-------+-------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                              |
      +-------+-------------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `a` float(20,4) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-------------------------------------------------------------------------------------------+
      

      CREATE OR REPLACE TABLE t2 SELECT b FROM t1 UNION SELECT b FROM t1;
      SHOW CREATE TABLE t2;
      

      +-------+-------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                              |
      +-------+-------------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `b` float(20,3) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-------------------------------------------------------------------------------------------+
      

      CREATE OR REPLACE TABLE t2 SELECT c FROM t1 UNION SELECT c FROM t1;
      SHOW CREATE TABLE t2;
      

      +-------+-------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                              |
      +-------+-------------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `c` float(20,4) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-------------------------------------------------------------------------------------------+
      

      CREATE OR REPLACE TABLE t2 SELECT c FROM t1 UNION SELECT a FROM t1;
      SHOW CREATE TABLE t2;
      

      +-------+-------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                              |
      +-------+-------------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `c` float(20,4) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-------------------------------------------------------------------------------------------+
      

      But fixed length is not preserved in UNION in case if the columns have different definitions. A non-fixed-length column is created:

      CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1;
      SHOW CREATE TABLE t2;
      

      +-------+-------------------------------------------------------------------------------------+
      | Table | Create Table                                                                        |
      +-------+-------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `a` float DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-------------------------------------------------------------------------------------+
      

      CREATE OR REPLACE TABLE t2 AS SELECT b FROM t1 UNION SELECT a FROM t1;
      SHOW CREATE TABLE t2;
      

      +-------+-------------------------------------------------------------------------------------+
      | Table | Create Table                                                                        |
      +-------+-------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `b` float DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-------------------------------------------------------------------------------------+
      

      Hybrid functions and UNION should return the same column types.

      The code in Item_type_holder::join_types was written in 4.1 time and was not changed significantly since that. So it implements simplified data type aggregation rules.
      The code in Item_func::count_real_length was notably improved since that and returns more precise data types.

      It would be nice to reuse hybrid function aggregation code for UNION.

        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: