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

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

    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

          blocks

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-4912 Data type plugin API version 1

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-12861 FIRST_VALUE() does not preserve the exact data type

          • Major - Major loss of function.
          • Closed

          Activity

            Transition Time In Source Status Execution Times
            Alexander Barkov made transition -
            Open In Progress
            		6h 31m 1
            Alexander Barkov made transition -
            In Progress In Review
            		11s 1
            Oleksandr Byelkin made transition -
            In Review Stalled
            		4d 17h 8m 1
            Alexander Barkov made transition -
            Stalled In Progress
            		22h 57m 1
            Alexander Barkov made transition -
            In Progress Closed
            		11s 1

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.