[MDEV-8873] Wrong field type or metadata for LEAST(int_column,string_column) Created: 2015-09-30  Updated: 2015-10-08  Resolved: 2015-10-01

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.0, 10.1
Fix Version/s: 10.1.8

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: upstream

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.

 Comments   
Comment by Alexander Barkov [ 2015-10-01 ]

Repeatable in MySQL-5.7.8

Generated at Thu Feb 08 07:30:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.