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

VIEW over a ROLLUP query reports too large columns

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1, 10.2
    • Fix Version/s: 10.3.1
    • Component/s: OTHER
    • Labels:
      None

      Description

      DROP TABLE IF EXISTS t1;
      DROP VIEW IF EXISTS v1;
      CREATE TABLE t1 (a int(10) NOT NULL, b int(20) NOT NULL);
      INSERT INTO t1 VALUES (1,1),(2,2);
      CREATE VIEW v1 AS SELECT a,b FROM t1;
      DESC v1;

      returns

      +-------+---------+------+-----+---------+-------+
      | Field | Type    | Null | Key | Default | Extra |
      +-------+---------+------+-----+---------+-------+
      | a     | int(10) | NO   |     | NULL    |       |
      | b     | int(20) | NO   |     | NULL    |       |
      +-------+---------+------+-----+---------+-------+

      int(10) and int(20) looks OK as the data type.

      Now if I add GROUP BY into the VIEW definition:

      DROP VIEW IF EXISTS v1;
      CREATE VIEW v1 AS
      SELECT a, b FROM t1 GROUP BY a,b;
      DESC v1;

      it still returns the same data types:

      +-------+---------+------+-----+---------+-------+
      | Field | Type    | Null | Key | Default | Extra |
      +-------+---------+------+-----+---------+-------+
      | a     | int(10) | NO   |     | NULL    |       |
      | b     | int(20) | NO   |     | NULL    |       |
      +-------+---------+------+-----+---------+-------+

      Looks OK so far.

      Now if I add ROLLUP into the VIEW definition as follows:

      DROP VIEW IF EXISTS v1;
      CREATE VIEW v1 AS
      SELECT a, b FROM t1 GROUP BY a,b WITH ROLLUP;
      DESC v1;

      it returns different data types:

      +-------+------------+------+-----+---------+-------+
      | Field | Type       | Null | Key | Default | Extra |
      +-------+------------+------+-----+---------+-------+
      | a     | bigint(11) | YES  |     | NULL    |       |
      | b     | bigint(20) | YES  |     | NULL    |       |
      +-------+------------+------+-----+---------+-------+

      bigint looks too large. int should be enough, as in the GROUP BY query with no ROLLUP.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: