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

CREATE TABLE..SELECT creates different columns for IFNULL() and equivalent COALESCE,CASE,IF

    Details

      Description

      CASE and its abbreviations IFNULL, IF, NULLIF, COALESCE aggregate data types of multiple arguments.
      They're expected to return the same result type for the same input types.
      But currently there are multiple implementations for of the aggregating code in:

      Item_func_case_abbreviations2::if::fix_length_and_dec()   (shared by Item_func_if, Item_func_ifnull)
      Item_func_coalesce::fix_length_and_dec()  
      Item_func_case::fix_length_and_dec()

      This result in different behavior:

      DROP TABLE IF EXISTS t1,t2;
      CREATE TABLE t1 (a SMALLINT);
      INSERT INTO t1 VALUES (1),(2);
      CREATE TABLE t2 AS SELECT
        IFNULL(a,a) AS c1,
        COALESCE(a,a) AS c2,
        CASE WHEN a IS NOT NULL THEN a ELSE a END AS c3,
        IF(a IS NULL,a,a) AS c4 FROM t1;
      SHOW CREATE TABLE t2;

      returns

      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                   |
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `c1` smallint(6) DEFAULT NULL,
        `c2` int(6) DEFAULT NULL,
        `c3` int(6) DEFAULT NULL,
        `c4` int(6) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      Notice, IFNULL preserves the data type, while the other functions do not.

      If I run this script in "mysql --column-type-info":

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a SMALLINT);
      INSERT INTO t1 VALUES (1),(2);
      SELECT
        IFNULL(a,a) AS c1,
        COALESCE(a,a) AS c2,
        CASE WHEN a IS NOT NULL THEN a ELSE a END AS c3,
        IF(a IS NULL,a,a) AS c4
      FROM t1;

      it correctly reports SHORT as the data type for all columns:

      Field   1:  `c1`
      Catalog:    `def`
      Database:   ``
      Table:      ``
      Org_table:  ``
      Type:       SHORT
      Collation:  binary (63)
      Length:     6
      Max_length: 1
      Decimals:   0
      Flags:      BINARY NUM 
       
      Field   2:  `c2`
      Catalog:    `def`
      Database:   ``
      Table:      ``
      Org_table:  ``
      Type:       SHORT
      Collation:  binary (63)
      Length:     6
      Max_length: 1
      Decimals:   0
      Flags:      BINARY NUM 
       
      Field   3:  `c3`
      Catalog:    `def`
      Database:   ``
      Table:      ``
      Org_table:  ``
      Type:       SHORT
      Collation:  binary (63)
      Length:     6
      Max_length: 1
      Decimals:   0
      Flags:      BINARY NUM 
       
      Field   4:  `c4`
      Catalog:    `def`
      Database:   ``
      Table:      ``
      Org_table:  ``
      Type:       SHORT
      Collation:  binary (63)
      Length:     6
      Max_length: 1
      Decimals:   0
      Flags:      BINARY NUM 

      This script demonstrates the opposite behavior, when IFNULL returns a wrong column type, while the other function return the same data type as the expression not wrapped to hybrid functions:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 AS SELECT
        connection_id() AS c0,
        IFNULL(connection_id(),connection_id()) AS c1,
        COALESCE(connection_id(), connection_id()) AS c2,
        CASE WHEN 0 THEN connection_id() ELSE connection_id() END AS c3,
        IF(0,connection_id(),connection_id()) AS c4;
      SHOW CREATE TABLE t1;

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

      Notice, c0 is int(10), so are c2,c3,c4, but c1 created from IFNULL() is bigint(10). It should be int(10).

      Proposal:

      • Split count_string_result_length() and friends into two steps:
        a. on the first step it will actually count attributes, this step will go into Type_handler_xxx::join_type_attributes()
        b. on the second step it will install converters into the argument list, e.g. Item_func_conv_charset when character set conversion is needed.
      • Unify all CASE-alike hybrid functions:
        CASE, IF, NULLIF, IFNULL, COALESCE and possibly LEAST, GREATEST
        to go through join_type_attributes(). Currently many of them have their own fix_length_and_dec() implementation, which actually do exactly the same thing, but in different ways.

        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: