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

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (val bit(1));

INSERT INTO t1 VALUES (0);

DROP TABLE IF EXISTS t2;

CREATE TABLE t2 AS SELECT COALESCE(val, 1) AS c FROM t1;

SELECT  * FROM t2;

SHOW CREATE TABLE t2;

returns

+------+

| c    |

+------+

|    0 |

+------+

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

| Table | Create Table                                                                               |

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

| t2    | CREATE TABLE `t2` (

  `c` decimal(1,0) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

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

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

SELECT COALESCE(val, 1) FROM t1;

it returns the following result and metadata:

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

| COALESCE(val, 1) |

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

| 0                |

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

Field   1:  `COALESCE(val, 1)`

...

Type:       VAR_STRING

Collation:  binary (63)

Length:     1

Max_length: 1

Decimals:   31

Flags:      UNSIGNED BINARY

Notice, COALESCE(val,1) positions itself as:

The expected result is to return the same data type in both cases

Note, a UNION between a BIT column and an INT literal creates a column of type VARBINARY(20):

DROP TABLE IF EXISTS t1, t2;

CREATE TABLE t1 (val BIT(1));

CREATE TABLE t2 AS SELECT val FROM t1 UNION SELECT 1;

SHOW CREATE TABLE t2;

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

| Table | Create Table                                                                                  |

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

| t2    | CREATE TABLE `t2` (

  `val` varbinary(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

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

which is closer to what metadata returns for COALESCE(val,1) rather than what CREATE TABLE..SELECT returns for COALESCE(val,1).

Summary:

SELECT bit_column FROM t1 - returns a string-alike value with BIT type in metadata
SELECT bit_column FROM t1 UNION SELECT 1 – returns a number-alike value with VAR_STRING data type in metadata
SELECT COALESCE(bit_column,1) FROM t1 – returns a number-alike value with VAR_STRING metadata
CREATE TABLE t2 AS SELECT val FROM t1 UNION SELECT 1; – creates a column of type VARBINARY(20), with number-alike values
CREATE TABLE t2 AS SELECT COALESCE(val,1) FROM t1; – creates a column of type DECIMAL(1,0)

duplicates

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

relates to

MDEV-32244 Wrong bit encoding using COALESCE

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

MariaDB Server