[MDEV-21662] UNION to sub-query with constant NULL incorrectly cast unsigned INT to DECIMAL(48,38) - Jira

XML

Word

Printable

To reproduce, first create and populate tables like this:

CREATE TABLE `tab1` (

  `col1` varchar(20) NOT NULL,

  `col2` varchar(45) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `tab1` (`col1`, `col2`)

VALUES('a', 'AAA'), ('a', 'BBB'), ('b', 'CCC');

CREATE TABLE `tab2` (

  `col1` int NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `tab2` (`col1`)

VALUES (1), (2), (3);

Then, execute the following select statement:

SELECT

	`tab2`.`col1`,

	'FOO' AS `col2`

FROM `tab2`

UNION

SELECT

	`temp`.`col1`,

	`temp`.`col2`

FROM (

	SELECT

		NULL AS `col1`,

		GROUP_CONCAT(`tab1`.`col2`) AS `col2`

	FROM `tab1`

	GROUP BY `tab1`.`col1`

 ) AS `temp`

Which will incorrectly return the following:

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

| col1                                     | col2    |

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

| 1.00000000000000000000000000000000000000 | FOO     |

| 2.00000000000000000000000000000000000000 | FOO     |

| 3.00000000000000000000000000000000000000 | FOO     |

|                                     NULL | AAA,BBB |

|                                     NULL | CCC     |

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

Whereas the expected result should have been:

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

| col1 | col2    |

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

|    1 | FOO     |

|    2 | FOO     |

|    3 | FOO     |

| NULL | AAA,BBB |

| NULL | CCC     |

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

When a table is created from the query, it's clear that the column is incorrectly being cast as decimal(48,38):

CREATE OR REPLACE TABLE tab3 AS SELECT

	`tab2`.`col1`,

	'FOO' AS `col2`

FROM `tab2`

UNION

SELECT

	`temp`.`col1`,

	`temp`.`col2`

FROM (

	SELECT

		NULL AS `col1`,

		GROUP_CONCAT(`tab1`.`col2`) AS `col2`

	FROM `tab1`

	GROUP BY `tab1`.`col1`

 ) AS `temp`;

SHOW CREATE TABLE tab3;

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

| Table | Create Table                                     |

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

| tab3  | CREATE TABLE `tab3` (

  `col1` decimal(48,38) DEFAULT NULL,

  `col2` mediumtext DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

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

relates to

MDEV-12775 Reuse data type aggregation code for hybrid functions and UNION

links to

SO user experience problem

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