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

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0, 10.1
    • 10.1.8
    • OTHER
    • 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

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