Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21662

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

    XMLWordPrintable

    Details

      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

        1. docker-compose.yml
          0.3 kB
          Taylor Honsowetz
        2. my.cnf
          2 kB
          Taylor Honsowetz

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              honso Taylor Honsowetz
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated: