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

VIEW over a ROLLUP query reports too large columns

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1(EOL), 10.2(EOL)
    • 10.3.1
    • OTHER
    • 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

          blocks

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-4912 Data type plugin API version 1

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-12886 Different data type and default for INT and BIGINT columns in a VIEW for a SELECT with ROLLUP

          • Major - Major loss of function.
          • Closed

          Activity

            bar Alexander Barkov added a comment -

            The fix for MDEV-12886 fixed this problem as well. Adding tests only.

            Pushed into bb-10.2-ext

            bar Alexander Barkov added a comment - The fix for MDEV-12886 fixed this problem as well. Adding tests only. Pushed into bb-10.2-ext

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.