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

Unexpected data type and truncation when using CTE

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.2, 10.3
    • Fix Version/s: 10.3
    • Component/s: Optimizer - CTE
    • Labels:
      None

      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

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated: