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

[MDEV-21662] UNION to sub-query with constant NULL incorrectly cast unsigned INT to DECIMAL(48,38) Created: 2020-02-04 Updated: 2023-12-20
Status:	Open
Project:	MariaDB Server
Component/s:	Data Manipulation - Subquery, Data types, Parser
Affects Version/s:	10.3, 10.4, 10.11.6
Fix Version/s:	10.4, 10.5, 10.11

Type:

Bug

Priority:

Minor

Reporter:

Taylor Honsowetz

Assignee:

Alexander Barkov

Resolution:

Unresolved

Votes:

Labels:

regression, regression-10.2

Environment:

mariadb:10.4.11-bionic container

Attachments:

docker-compose.yml

my.cnf

Issue Links:

Relates
relates to	~~MDEV-12775~~	Reuse data type aggregation code for ...	Closed

Description

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 |

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

Comments

Comment by Elena Stepanova [ 2020-03-07 ]

Please paste or attach your cnf file(s).

MariaDB [test]> 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`;

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

| col1 | col2    |

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

|    1 | FOO     |

|    2 | FOO     |

|    3 | FOO     |

| NULL | AAA,BBB |

| NULL | CCC     |

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

5 rows in set (0.001 sec)

MariaDB [test]>  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`;

Query OK, 5 rows affected (0.253 sec)

Records: 5  Duplicates: 0  Warnings: 0

MariaDB [test]>

MariaDB [test]> SHOW CREATE TABLE tab3;

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

| Table | Create Table                                                                                                                 |

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

| tab3  | CREATE TABLE `tab3` (

  `col1` int(11) DEFAULT NULL,

  `col2` mediumtext DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

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

1 row in set (0.000 sec)

MariaDB [test]> select @@version;

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

| @@version       |

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

| 10.4.11-MariaDB |

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

1 row in set (0.000 sec)

Comment by Taylor Honsowetz [ 2020-03-09 ]

I've attached my cnf file along with my docker-compose.yml

Comment by Alice Sherepa [ 2020-04-17 ]

I can reproduce on 10.3-10.5 if in the table tab2 column is defined as unsigned int. (Somehow unsigned is mentioned in the summary of bug, but not in table definition).

create table t1 (col1 varchar(20) not null, col2 varchar(45));

insert into t1 values('a','aaa'),('a','bbb'),('b','ccc');

create table t2 (col1 int unsigned not null);

insert into t2 (col1) values (1), (2), (3);

select t2.col1, 'foo' as col2 from t2

union

select temp.col1, temp.col2 from

	(select null as col1, group_concat(t1.col2) as col2 from t1

		group by t1.col1 ) as temp;

MariaDB [test]> CREATE TABLE `tab1` ( `col1` varchar(20) NOT NULL, `col2` varchar(45) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query OK, 0 rows affected (0.034 sec)

MariaDB [test]> INSERT INTO `tab1` (`col1`, `col2`)

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

Query OK, 3 rows affected (0.005 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> CREATE TABLE `tab2` ( `col1` int unsigned NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query OK, 0 rows affected (0.033 sec)

MariaDB [test]> INSERT INTO `tab2` (`col1`)  VALUES (1), (2), (3);

Query OK, 3 rows affected (0.006 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> 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`;

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

| col1                                     | col2    |

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

| 1.00000000000000000000000000000000000000 | FOO     |

| 2.00000000000000000000000000000000000000 | FOO     |

| 3.00000000000000000000000000000000000000 | FOO     |

|                                     NULL | AAA,BBB |

|                                     NULL | CCC     |

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

5 rows in set (0.002 sec)

Comment by Daniel Black [ 2023-12-20 ]

Isn't this the simpler form:

10.3.39,10.4.32,10.11.6
MariaDB [test]> CREATE TABLE mu (x BIGINT(20) UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.016 sec)

MariaDB [test]> CREATE TABLE ms (x BIGINT(20) SIGNED NOT NULL);
Query OK, 0 rows affected (0.019 sec)

MariaDB [test]> SELECT x FROM ms UNION SELECT NULL;
Field 1: `x`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: LONGLONG
Collation: binary (63)
Length: 20
Max_length: 0
Decimals: 0
Flags: NUM


+------+
\| x \|
+------+
\| NULL \|
+------+
1 row in set (0.001 sec)

MariaDB [test]> SELECT x FROM mu UNION SELECT NULL;
Field 1: `x`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: NEWDECIMAL
Collation: binary (63)
Length: 21
Max_length: 0
Decimals: 0
Flags: NUM


+------+
\| x \|
+------+
\| NULL \|
+------+
1 row in set (0.001 sec)

10.2.44 keeps the type
MariaDB [test]> CREATE TABLE mu (x BIGINT(20) UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> CREATE TABLE ms (x BIGINT(20) SIGNED NOT NULL);
Query OK, 0 rows affected (0.48 sec)

MariaDB [test]> SELECT x FROM mu UNION SELECT NULL;
Field 1: `x`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: LONGLONG
Collation: binary (63)
Length: 20
Max_length: 0
Decimals: 0
Flags: UNSIGNED NUM


+------+
\| x \|
+------+
\| NULL \|
+------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT x FROM ms UNION SELECT NULL;
Field 1: `x`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: LONGLONG
Collation: binary (63)
Length: 20
Max_length: 0
Decimals: 0
Flags: NUM


+------+
\| x \|
+------+
\| NULL \|
+------+
1 row in set (0.00 sec)

UNSIGNED numbers are converted to DECIMAL when UNION with NULL while SIGNED are kept as LONGLONG.

Generated at Thu Feb 08 09:08:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-21662] UNION to sub-query with constant NULL incorrectly cast unsigned INT to DECIMAL(48,38) Created: 2020-02-04 Updated: 2023-12-20