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

Unexpected data type and truncation when using CTE

    XMLWordPrintable

Details

    Description

      I have a table with a company employee hierarchy:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1
      (
        id INT,     -- employee ID
        mid INT,    -- manager ID, or NULL if top level employee
        name TEXT   -- employee name
      );
      INSERT INTO t1 VALUES (0,NULL, 'Name');
      INSERT INTO t1 VALUES (1,0,    'Name1');
      INSERT INTO t1 VALUES (2,0,    'Name2');
      INSERT INTO t1 VALUES (11,1,   'Name11');
      INSERT INTO t1 VALUES (12,1,   'Name12');
      

      Now I want to print the company hierarchy as a table that additionally includes employee hierarchy levels and manager names. I use a recursive CTE for that:

      DROP TABLE IF EXISTS t2;
      CREATE TABLE t2 AS
      WITH RECURSIVE
      cteReports (level, id, mid, name) AS
      (
        SELECT 1, id, mid, name FROM t1 WHERE mid IS NULL
        UNION ALL
        SELECT r.level + 1, e.id, e.mid, e.name FROM t1 e
        INNER JOIN cteReports r ON e.mid = r.id
      )
      SELECT
        level, id, mid, name,
        (SELECT name FROM t1 WHERE id= cteReports.mid) AS mname
      FROM cteReports 
      ORDER BY level, mid;
      SHOW CREATE TABLE t2;
      SELECT * FROM t2;
      

      It creates a table with the expected structure:

      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                                                          |
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `level` bigint(1) NOT NULL DEFAULT 0,
        `id` int(11) DEFAULT NULL,
        `mid` int(11) DEFAULT NULL,
        `name` text DEFAULT NULL,
        `mname` text DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

      and with correct data:

      +-------+------+------+--------+-------+
      | level | id   | mid  | name   | mname |
      +-------+------+------+--------+-------+
      |     1 |    0 | NULL | Name   | NULL  |
      |     2 |    1 |    0 | Name1  | Name  |
      |     2 |    2 |    0 | Name2  | Name  |
      |     3 |   11 |    1 | Name11 | Name1 |
      |     3 |   12 |    1 | Name12 | Name1 |
      +-------+------+------+--------+-------+
      

      Now I want for some reasons to add some big number to manager IDs (mid):

      DROP TABLE IF EXISTS t2;
      CREATE TABLE t2 AS
      WITH RECURSIVE
      cteReports (level, id, mid, name) AS
      (
        SELECT 1, id, mid, name FROM t1 WHERE mid IS NULL
        UNION ALL
        SELECT r.level + 1, e.id, e.mid + 1000000000000, e.name FROM t1 e
        INNER JOIN cteReports r ON e.mid = r.id
      )
      SELECT
        level, id, mid, name,
        (SELECT name FROM t1 WHERE id= cteReports.mid) AS mname
      FROM cteReports 
      ORDER BY level, mid;
      SHOW CREATE TABLE t2;
      SELECT * FROM t2;
      

      It creates exactly the same table to the one in the previous script and truncates data silently:

      +-------+------+------------+--------+-------+
      | level | id   | mid        | name   | mname |
      +-------+------+------------+--------+-------+
      |     1 |    0 |       NULL | Name   | NULL  |
      |     2 |    1 | 2147483647 | Name1  | NULL  |
      |     2 |    2 | 2147483647 | Name2  | NULL  |
      |     3 |   11 | 2147483647 | Name11 | NULL  |
      |     3 |   12 | 2147483647 | Name12 | NULL  |
      +-------+------+------------+--------+-------+
      

      This looks wrong. Instead if INT, the expected column type for mid should be BIGINT or DECIMAL, and no data truncation should happen.

      Attachments

        Activity

          People

            monty Michael Widenius
            bar Alexander Barkov
            Votes:
            2 Vote for this issue
            Watchers:
            10 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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