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

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

        Details

        • Type: Bug
        • Status: Closed
        • 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.

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                bar Alexander Barkov added a comment -

                Repeatable in MySQL-5.7.8

                Show
                bar Alexander Barkov added a comment - Repeatable in MySQL-5.7.8

                  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:

                      Agile