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

CASE and CASE-alike hybrid functions do not preserve exact data types

    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 problem is repeatable with CASE, COALESCE, IF, NULLIF, LEAST, GREATEST.
      This problem is not repeatable with IFNULL, which does preserve exact data types.

      FLOAT is converted to DOUBLE

      CREATE OR REPLACE TABLE t1 (a FLOAT(10,2));
      CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1;
      DESCRIBE t2;
      

      +-------------+--------------+------+-----+---------+-------+
      | Field       | Type         | Null | Key | Default | Extra |
      +-------------+--------------+------+-----+---------+-------+
      | COALESCE(a) | double(10,2) | YES  |     | NULL    |       |
      +-------------+--------------+------+-----+---------+-------+
      

      CREATE OR REPLACE TABLE t1 (a FLOAT(10,2));
      CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1;
      DESCRIBE t2;
      

      +------------+--------------+------+-----+---------+-------+
      | Field      | Type         | Null | Key | Default | Extra |
      +------------+--------------+------+-----+---------+-------+
      | LEAST(a,a) | double(19,2) | YES  |     | NULL    |       |
      +------------+--------------+------+-----+---------+-------+
      

      TINYINT is converted to INT

      CREATE OR REPLACE TABLE t1 (a TINYINT(1));
      CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1;
      DESCRIBE t2;
      

      +-------------+--------+------+-----+---------+-------+
      | Field       | Type   | Null | Key | Default | Extra |
      +-------------+--------+------+-----+---------+-------+
      | COALESCE(a) | int(4) | YES  |     | NULL    |       |
      +-------------+--------+------+-----+---------+-------+
      

      CREATE OR REPLACE TABLE t1 (a TINYINT(1));
      CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1;
      DESCRIBE t2;
      

      +------------+--------+------+-----+---------+-------+
      | Field      | Type   | Null | Key | Default | Extra |
      +------------+--------+------+-----+---------+-------+
      | LEAST(a,a) | int(4) | YES  |     | NULL    |       |
      +------------+--------+------+-----+---------+-------+
      

      UNION

      Unlike hybrid functions, UNION correctly preserves the exact data types:

      CREATE OR REPLACE TABLE t1 (a FLOAT(10,2));
      CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1;
      DESCRIBE t2;
      

      +-------+-------------+------+-----+---------+-------+
      | Field | Type        | Null | Key | Default | Extra |
      +-------+-------------+------+-----+---------+-------+
      | a     | float(10,2) | YES  |     | NULL    |       |
      +-------+-------------+------+-----+---------+-------+
      

      CREATE OR REPLACE TABLE t1 (a TINYINT(1));
      CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1;
      DESCRIBE t2;
      

      +-------+------------+------+-----+---------+-------+
      | Field | Type       | Null | Key | Default | Extra |
      +-------+------------+------+-----+---------+-------+
      | a     | tinyint(4) | YES  |     | NULL    |       |
      +-------+------------+------+-----+---------+-------+
      

      Hybrid functions should be fixed to preserve the exact data types, like UNION does.

      Note, UNION is probably wrong with returning tinyint(4) instead of tinyint(1). But this problem will be addressed separately.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: