[MDEV-8910] Wrong metadata or field type for MAX(COALESCE(string_field)) - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL)
Fix Version/s: 10.1.8
Component/s: OTHER
Labels:
None

Sprint:
10.1.8-4

Description

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (

  c1 LONGBLOB,

  c2 MEDIUMBLOB,

  c3 BLOB,

  c4 TINYBLOB,

  c5 VARCHAR(64),

  c6 VARCHAR(4000),

  c7 CHAR(64));

DROP TABLE IF EXISTS t2;

CREATE TABLE t2 AS SELECT

  MAX(COALESCE(c1)) AS c1,

  MAX(COALESCE(c2)) AS c2,

  MAX(COALESCE(c3)) AS c3,

  MAX(COALESCE(c4)) AS c4,

  MAX(COALESCE(c5)) AS c5,

  MAX(COALESCE(c6)) AS c6,

  MAX(COALESCE(c7)) AS c7 FROM t1;

SHOW CREATE TABLE t2;

returns

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                                                  |

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| t2    | CREATE TABLE `t2` (

  `c1` longblob,

  `c2` mediumblob,

  `c3` blob,

  `c4` varbinary(255) DEFAULT NULL,

  `c5` varchar(64) DEFAULT NULL,

  `c6` text,

  `c7` varchar(64) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Now if I run "mysql --column-type-info test" and execute this query:

SELECT

  MAX(COALESCE(c1)) AS c1,

  MAX(COALESCE(c2)) AS c2,

  MAX(COALESCE(c3)) AS c3,

  MAX(COALESCE(c4)) AS c4,

  MAX(COALESCE(c5)) AS c5,

  MAX(COALESCE(c6)) AS c6,

  MAX(COALESCE(c7)) AS c7 FROM t1;

it returns the following metadata:

Field   1:  `c1`

Type:       LONG_BLOB

Collation:  binary (63)

Length:     4294967295

Field   2:  `c2`

Type:       MEDIUM_BLOB

Collation:  binary (63)

Length:     16777215

Field   3:  `c3`

Type:       VAR_STRING

Collation:  binary (63)

Length:     65535

Field   4:  `c4`

Type:       VAR_STRING

Collation:  binary (63)

Length:     255

Field   5:  `c5`

Type:       VAR_STRING

Collation:  utf8_general_ci (33)

Length:     192

Field   6:  `c6`

Type:       VAR_STRING

Collation:  utf8_general_ci (33)

Length:     12000

Field   7:  `c7`

Type:       VAR_STRING

Collation:  utf8_general_ci (33)

Length:     192

Notice, for some columns the type of the column created in CREATE TABLE...SELECT does not match the type of the column returned in the metadata for the same SELECT query.

Attachments

Issue Links

is duplicated by

MDEV-8862 Wrong field type for MAX(COALESCE(datetime_column))

Closed

MDEV-8867 Wrong field type or metadata for COALESCE(bit_column, 1)

Closed

Activity

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2015-10-07 00:06

Updated:: 2017-11-07 08:58

Resolved:: 2015-10-07 10:49

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server