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

Details

Type: Bug
Status: Open (View Workflow)
Priority: Minor
Resolution: Unresolved
Affects Version/s: 10.11.6, 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.5, 10.11
Component/s: Data Manipulation - Subquery, Data types, Parser
Labels:
- regression
- regression-10.2
Environment:
mariadb:10.4.11-bionic container

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 |

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

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

docker-compose.yml
0.3 kB
2020-03-09 11:50
my.cnf
2 kB
2020-03-09 11:46

Issue Links

relates to

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

Closed

links to

SO user experience problem

Activity

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2020-03-07 15:53

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)

Elena Stepanova added a comment - 2020-03-07 15:53 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)

Taylor Honsowetz added a comment - 2020-03-09 11:51

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

Taylor Honsowetz added a comment - 2020-03-09 11:51 I've attached my cnf file along with my docker-compose.yml

Alice Sherepa added a comment - 2020-04-17 11:00

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)

Alice Sherepa added a comment - 2020-04-17 11:00 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)

Daniel Black added a comment - 2023-12-20 02:01

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.

Daniel Black added a comment - 2023-12-20 02:01 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.

MariaDB Server

UNION to sub-query with constant NULL incorrectly cast unsigned INT to DECIMAL(48,38)

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration