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

Wrong field type or metadata for LEAST(int_column,string_column)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1, 10.0
    • Fix Version/s: 10.1.8
    • Component/s: OTHER
    • Labels:
    • Sprint:
      10.1.8-4

      Description

      If I start "mysql --column-type-info test" and run this script:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT, b VARCHAR(10));
      INSERT INTO t1 VALUES (-2147483648,'100x');
      SELECT LEAST(a,b),GREATEST(a,b) FROM t1;

      it returns the following metadata:

      Field   1:  `LEAST(a,b)`
      ...
      Type:       VAR_STRING
      Collation:  binary (63)
      Length:     23
      Max_length: 11
      Decimals:   31
      Flags:      BINARY 
       
      Field   2:  `GREATEST(a,b)`
      ...
      Type:       VAR_STRING
      Collation:  binary (63)
      Length:     23
      Max_length: 3
      Decimals:   31
      Flags:      BINARY 

      That is the columns are reported to be of the VARBINARY data type.

      Now if I do CREATE TABLE..SELECT:

      DROP TABLE IF EXISTS t2;
      CREATE TABLE t2 AS SELECT LEAST(a,b),GREATEST(a,b) FROM t1;
      SHOW CREATE TABLE t2;

      it creates double columns:

      +-------+--------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                         |
      +-------+--------------------------------------------------------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `LEAST(a,b)` double DEFAULT NULL,
        `GREATEST(a,b)` double DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+--------------------------------------------------------------------------------------------------------------------------------------+

      Summary:

      • metadata reports the VARBINARY data type
      • CREATE TABLE .. SELECT creates columns of the DOUBLE data types.

      The expected result would be to have the same data type in both cases.
      DOUBLE looks the best candidate.

        Attachments

          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: