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

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            bar Alexander Barkov made changes -
            Summary DESCIBE is not consistent about nullability of INT(10) vs INT(20) column with a ROLLUP query DESCRIBE is not consistent about nullability of INT(10) vs INT(20) column with a ROLLUP query
            bar Alexander Barkov made changes -
            Summary DESCRIBE is not consistent about nullability of INT(10) vs INT(20) column with a ROLLUP query DESCRIBE is not consistent about nullability of INT(10) vs INT(20) column of a VIEW with a ROLLUP query
            bar Alexander Barkov made changes -
            Description {code}
            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;
            {code}
            returns
            {noformat}
            +-------+---------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+---------+------+-----+---------+-------+
            | a | int(10) | NO | | NULL | |
            | b | int(20) | NO | | NULL | |
            +-------+---------+------+-----+---------+-------+
            {noformat}
            Both columns are correctly reported as NOT NULL, but NULL as Default looks wrong.

            Now if I change the VIEW definition as follows:
            {code}
            DROP VIEW IF EXISTS v1;
            CREATE VIEW v1 AS
            SELECT a, b FROM t1 GROUP BY a,b WITH ROLLUP;
            DESC v1;
            {code}
            it returns
            {noformat}
            +-------+------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+------------+------+-----+---------+-------+
            | a | int(11) | YES | | 0 | |
            | b | bigint(20) | YES | | NULL | |
            +-------+------------+------+-----+---------+-------+
            {noformat}
            Both columns are reported as NULL, which looks wrong.
            Additionally, the two columns have different Default value: 0 vs NULL.
            		 In 10.1:
            {code}
            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;
            {code}
            returns
            {noformat}
            +-------+---------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+---------+------+-----+---------+-------+
            | a | int(10) | NO | | NULL | |
            | b | int(20) | NO | | NULL | |
            +-------+---------+------+-----+---------+-------+
            {noformat}
            Both columns are correctly reported as NOT NULL, but NULL as Default looks wrong.

            Now if I change the VIEW definition as follows:
            {code}
            DROP VIEW IF EXISTS v1;
            CREATE VIEW v1 AS
            SELECT a, b FROM t1 GROUP BY a,b WITH ROLLUP;
            DESC v1;
            {code}
            it returns
            {noformat}
            +-------+------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+------------+------+-----+---------+-------+
            | a | int(11) | YES | | 0 | |
            | b | bigint(20) | YES | | NULL | |
            +-------+------------+------+-----+---------+-------+
            {noformat}
            Both columns are reported as NULL, which looks wrong.
            Additionally, the two columns have different Default value: 0 vs NULL.
            bar Alexander Barkov made changes -
            Description In 10.1:
            {code}
            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;
            {code}
            returns
            {noformat}
            +-------+---------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+---------+------+-----+---------+-------+
            | a | int(10) | NO | | NULL | |
            | b | int(20) | NO | | NULL | |
            +-------+---------+------+-----+---------+-------+
            {noformat}
            Both columns are correctly reported as NOT NULL, but NULL as Default looks wrong.

            Now if I change the VIEW definition as follows:
            {code}
            DROP VIEW IF EXISTS v1;
            CREATE VIEW v1 AS
            SELECT a, b FROM t1 GROUP BY a,b WITH ROLLUP;
            DESC v1;
            {code}
            it returns
            {noformat}
            +-------+------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+------------+------+-----+---------+-------+
            | a | int(11) | YES | | 0 | |
            | b | bigint(20) | YES | | NULL | |
            +-------+------------+------+-----+---------+-------+
            {noformat}
            Both columns are reported as NULL, which looks wrong.
            Additionally, the two columns have different Default value: 0 vs NULL.
            		 In 10.2:
            {code}
            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;
            {code}
            returns
            {noformat}
            +-------+---------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+---------+------+-----+---------+-------+
            | a | int(10) | NO | | NULL | |
            | b | int(20) | NO | | NULL | |
            +-------+---------+------+-----+---------+-------+
            {noformat}
            Both columns are correctly reported as NOT NULL, but NULL as Default looks wrong.

            Now if I change the VIEW definition as follows:
            {code}
            DROP VIEW IF EXISTS v1;
            CREATE VIEW v1 AS
            SELECT a, b FROM t1 GROUP BY a,b WITH ROLLUP;
            DESC v1;
            {code}
            it returns
            {noformat}
            +-------+------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+------------+------+-----+---------+-------+
            | a | int(11) | YES | | 0 | |
            | b | bigint(20) | YES | | NULL | |
            +-------+------------+------+-----+---------+-------+
            {noformat}
            Both columns are reported as NULL, which looks wrong.
            Additionally, the two columns have different Default value: 0 vs NULL.
            bar Alexander Barkov made changes -
            Description In 10.2:
            {code}
            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;
            {code}
            returns
            {noformat}
            +-------+---------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+---------+------+-----+---------+-------+
            | a | int(10) | NO | | NULL | |
            | b | int(20) | NO | | NULL | |
            +-------+---------+------+-----+---------+-------+
            {noformat}
            Both columns are correctly reported as NOT NULL, but NULL as Default looks wrong.

            Now if I change the VIEW definition as follows:
            {code}
            DROP VIEW IF EXISTS v1;
            CREATE VIEW v1 AS
            SELECT a, b FROM t1 GROUP BY a,b WITH ROLLUP;
            DESC v1;
            {code}
            it returns
            {noformat}
            +-------+------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+------------+------+-----+---------+-------+
            | a | int(11) | YES | | 0 | |
            | b | bigint(20) | YES | | NULL | |
            +-------+------------+------+-----+---------+-------+
            {noformat}
            Both columns are reported as NULL, which looks wrong.
            Additionally, the two columns have different Default value: 0 vs NULL.
            		 In 10.2:
            {code}
            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;
            {code}
            returns
            {noformat}
            +-------+---------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+---------+------+-----+---------+-------+
            | a | int(10) | NO | | NULL | |
            | b | int(20) | NO | | NULL | |
            +-------+---------+------+-----+---------+-------+
            {noformat}
            Both columns are correctly reported as NOT NULL, but NULL as Default looks wrong.

            Now if I change the VIEW definition as follows:
            {code}
            DROP VIEW IF EXISTS v1;
            CREATE VIEW v1 AS
            SELECT a, b FROM t1 GROUP BY a,b WITH ROLLUP;
            DESC v1;
            {code}
            it returns
            {noformat}
            +-------+------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+------------+------+-----+---------+-------+
            | a | bigint(11) | YES | | NULL | |
            | b | bigint(20) | YES | | NULL | |
            +-------+------------+------+-----+---------+-------+
            {noformat}
            Both columns are reported as NULL, which looks wrong.
            bar Alexander Barkov made changes -
            Description In 10.2:
            {code}
            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;
            {code}
            returns
            {noformat}
            +-------+---------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+---------+------+-----+---------+-------+
            | a | int(10) | NO | | NULL | |
            | b | int(20) | NO | | NULL | |
            +-------+---------+------+-----+---------+-------+
            {noformat}
            Both columns are correctly reported as NOT NULL, but NULL as Default looks wrong.

            Now if I change the VIEW definition as follows:
            {code}
            DROP VIEW IF EXISTS v1;
            CREATE VIEW v1 AS
            SELECT a, b FROM t1 GROUP BY a,b WITH ROLLUP;
            DESC v1;
            {code}
            it returns
            {noformat}
            +-------+------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+------------+------+-----+---------+-------+
            | a | bigint(11) | YES | | NULL | |
            | b | bigint(20) | YES | | NULL | |
            +-------+------------+------+-----+---------+-------+
            {noformat}
            Both columns are reported as NULL, which looks wrong.
            		 In 10.2:
            {code}
            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;
            {code}
            returns
            {noformat}
            +-------+---------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+---------+------+-----+---------+-------+
            | a | int(10) | NO | | NULL | |
            | b | int(20) | NO | | NULL | |
            +-------+---------+------+-----+---------+-------+
            {noformat}
            Both columns are correctly reported as NOT NULL, but NULL as Default looks wrong.

            Now if I change the VIEW definition as follows:
            {code}
            DROP VIEW IF EXISTS v1;
            CREATE VIEW v1 AS
            SELECT a, b FROM t1 GROUP BY a,b WITH ROLLUP;
            DESC v1;
            {code}
            it returns
            {noformat}
            +-------+------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+------------+------+-----+---------+-------+
            | a | bigint(11) | YES | | NULL | |
            | b | bigint(20) | YES | | NULL | |
            +-------+------------+------+-----+---------+-------+
            {noformat}
            Both columns are reported as NULL, which is Ok (notice ROLLUP).
            bar Alexander Barkov made changes -
            Comment [ 10.1 works differently:
            {code}
            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 GROUP BY a,b WITH ROLLUP;
            DESC v1;
            {code}
            returns
            {noformat}
            +-------+------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+------------+------+-----+---------+-------+
            | a | bigint(11) | YES | | NULL | |
            | b | bigint(20) | YES | | NULL | |
            +-------+------------+------+-----+---------+-------+
            {noformat}

            But now if I add more columns into the view:
            {code}
            DROP VIEW IF EXISTS v1;
            CREATE VIEW v1 AS
            SELECT a, b, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
            DESC v1;
            {code}
            it returns different "Null" values for "a" and "b":
            {noformat}
            +-----------+------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-----------+------------+------+-----+---------+-------+
            | a | bigint(11) | YES | | NULL | |
            | b | int(20) | NO | | NULL | |
            | LENGTH(a) | bigint(10) | YES | | NULL | |
            | COUNT(*) | bigint(21) | NO | | 0 | |
            +-----------+------------+------+-----+---------+-------+
            {noformat}

            ]
            bar Alexander Barkov made changes -
            Component/s OTHER [ 10125 ]
            Fix Version/s 10.2.0 [ 20700 ]
            Fix Version/s 10.2 [ 14601 ]
            Resolution Not a Bug [ 6 ]
            Status Open [ 1 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            Resolution Not a Bug [ 6 ]
            Status Closed [ 6 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            Summary DESCRIBE is not consistent about nullability of INT(10) vs INT(20) column of a VIEW with a ROLLUP query VIEW over a ROLLUP query reports too large columns
            bar Alexander Barkov made changes -
            Description In 10.2:
            {code}
            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;
            {code}
            returns
            {noformat}
            +-------+---------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+---------+------+-----+---------+-------+
            | a | int(10) | NO | | NULL | |
            | b | int(20) | NO | | NULL | |
            +-------+---------+------+-----+---------+-------+
            {noformat}
            Both columns are correctly reported as NOT NULL, but NULL as Default looks wrong.

            Now if I change the VIEW definition as follows:
            {code}
            DROP VIEW IF EXISTS v1;
            CREATE VIEW v1 AS
            SELECT a, b FROM t1 GROUP BY a,b WITH ROLLUP;
            DESC v1;
            {code}
            it returns
            {noformat}
            +-------+------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+------------+------+-----+---------+-------+
            | a | bigint(11) | YES | | NULL | |
            | b | bigint(20) | YES | | NULL | |
            +-------+------------+------+-----+---------+-------+
            {noformat}
            Both columns are reported as NULL, which is Ok (notice ROLLUP).
            		 In 10.2:
            {code}
            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;
            {code}
            returns
            {noformat}
            +-------+---------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+---------+------+-----+---------+-------+
            | a | int(10) | NO | | NULL | |
            | b | int(20) | NO | | NULL | |
            +-------+---------+------+-----+---------+-------+
            {noformat}
            int(10) and int(20) looks OK as the data type.

            Now if I change the VIEW definition as follows:
            {code}
            DROP VIEW IF EXISTS v1;
            CREATE VIEW v1 AS
            SELECT a, b FROM t1 GROUP BY a,b WITH ROLLUP;
            DESC v1;
            {code}
            it returns
            {noformat}
            +-------+------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+------------+------+-----+---------+-------+
            | a | bigint(11) | YES | | NULL | |
            | b | bigint(20) | YES | | NULL | |
            +-------+------------+------+-----+---------+-------+
            {noformat}
            bigint looks large. int should be enough (at least for "a").
            bar Alexander Barkov made changes -
            Description In 10.2:
            {code}
            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;
            {code}
            returns
            {noformat}
            +-------+---------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+---------+------+-----+---------+-------+
            | a | int(10) | NO | | NULL | |
            | b | int(20) | NO | | NULL | |
            +-------+---------+------+-----+---------+-------+
            {noformat}
            int(10) and int(20) looks OK as the data type.

            Now if I change the VIEW definition as follows:
            {code}
            DROP VIEW IF EXISTS v1;
            CREATE VIEW v1 AS
            SELECT a, b FROM t1 GROUP BY a,b WITH ROLLUP;
            DESC v1;
            {code}
            it returns
            {noformat}
            +-------+------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+------------+------+-----+---------+-------+
            | a | bigint(11) | YES | | NULL | |
            | b | bigint(20) | YES | | NULL | |
            +-------+------------+------+-----+---------+-------+
            {noformat}
            bigint looks large. int should be enough (at least for "a").
            		 In 10.2:
            {code}
            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;
            {code}
            returns
            {noformat}
            +-------+---------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+---------+------+-----+---------+-------+
            | a | int(10) | NO | | NULL | |
            | b | int(20) | NO | | NULL | |
            +-------+---------+------+-----+---------+-------+
            {noformat}
            int(10) and int(20) looks OK as the data type.

            Now if I add GROUP BY into the VIEW definition:
            {code}
            DROP VIEW IF EXISTS v1;
            CREATE VIEW v1 AS
            SELECT a, b FROM t1 GROUP BY a,b;
            DESC v1;
            {code}
            it still returns the same data types:
            {noformat}
            +-------+---------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+---------+------+-----+---------+-------+
            | a | int(10) | NO | | NULL | |
            | b | int(20) | NO | | NULL | |
            +-------+---------+------+-----+---------+-------+
            {noformat}
            Looks OK so far.

            Now if I add ROLLUP into the VIEW definition as follows:
            {code}
            DROP VIEW IF EXISTS v1;
            CREATE VIEW v1 AS
            SELECT a, b FROM t1 GROUP BY a,b WITH ROLLUP;
            DESC v1;
            {code}
            it returns different data types:
            {noformat}
            +-------+------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+------------+------+-----+---------+-------+
            | a | bigint(11) | YES | | NULL | |
            | b | bigint(20) | YES | | NULL | |
            +-------+------------+------+-----+---------+-------+
            {noformat}
            bigint looks too large. int should be enough, as in the GROUP BY query with no ROLLUP.
            bar Alexander Barkov made changes -
            Comment [ Sorry, no bugs here. ]
            bar Alexander Barkov made changes -
            Description In 10.2:
            {code}
            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;
            {code}
            returns
            {noformat}
            +-------+---------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+---------+------+-----+---------+-------+
            | a | int(10) | NO | | NULL | |
            | b | int(20) | NO | | NULL | |
            +-------+---------+------+-----+---------+-------+
            {noformat}
            int(10) and int(20) looks OK as the data type.

            Now if I add GROUP BY into the VIEW definition:
            {code}
            DROP VIEW IF EXISTS v1;
            CREATE VIEW v1 AS
            SELECT a, b FROM t1 GROUP BY a,b;
            DESC v1;
            {code}
            it still returns the same data types:
            {noformat}
            +-------+---------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+---------+------+-----+---------+-------+
            | a | int(10) | NO | | NULL | |
            | b | int(20) | NO | | NULL | |
            +-------+---------+------+-----+---------+-------+
            {noformat}
            Looks OK so far.

            Now if I add ROLLUP into the VIEW definition as follows:
            {code}
            DROP VIEW IF EXISTS v1;
            CREATE VIEW v1 AS
            SELECT a, b FROM t1 GROUP BY a,b WITH ROLLUP;
            DESC v1;
            {code}
            it returns different data types:
            {noformat}
            +-------+------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+------------+------+-----+---------+-------+
            | a | bigint(11) | YES | | NULL | |
            | b | bigint(20) | YES | | NULL | |
            +-------+------------+------+-----+---------+-------+
            {noformat}
            bigint looks too large. int should be enough, as in the GROUP BY query with no ROLLUP.
            		 {code}
            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;
            {code}
            returns
            {noformat}
            +-------+---------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+---------+------+-----+---------+-------+
            | a | int(10) | NO | | NULL | |
            | b | int(20) | NO | | NULL | |
            +-------+---------+------+-----+---------+-------+
            {noformat}
            int(10) and int(20) looks OK as the data type.

            Now if I add GROUP BY into the VIEW definition:
            {code}
            DROP VIEW IF EXISTS v1;
            CREATE VIEW v1 AS
            SELECT a, b FROM t1 GROUP BY a,b;
            DESC v1;
            {code}
            it still returns the same data types:
            {noformat}
            +-------+---------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+---------+------+-----+---------+-------+
            | a | int(10) | NO | | NULL | |
            | b | int(20) | NO | | NULL | |
            +-------+---------+------+-----+---------+-------+
            {noformat}
            Looks OK so far.

            Now if I add ROLLUP into the VIEW definition as follows:
            {code}
            DROP VIEW IF EXISTS v1;
            CREATE VIEW v1 AS
            SELECT a, b FROM t1 GROUP BY a,b WITH ROLLUP;
            DESC v1;
            {code}
            it returns different data types:
            {noformat}
            +-------+------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+------------+------+-----+---------+-------+
            | a | bigint(11) | YES | | NULL | |
            | b | bigint(20) | YES | | NULL | |
            +-------+------------+------+-----+---------+-------+
            {noformat}
            bigint looks too large. int should be enough, as in the GROUP BY query with no ROLLUP.
            bar Alexander Barkov made changes -
            Issue Type Task [ 3 ] Bug [ 1 ]
            bar Alexander Barkov made changes -
            Affects Version/s 10.1 [ 16100 ]
            Affects Version/s 10.2 [ 14601 ]
            bar Alexander Barkov made changes -
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.2.0 [ 20700 ]
            bar Alexander Barkov made changes -
            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
            bar Alexander Barkov made changes -
            issue.field.resolutiondate 2017-05-27 12:50:28.0 2017-05-27 12:50:28.86
            bar Alexander Barkov made changes -
            Fix Version/s 10.3.1 [ 22532 ]
            Fix Version/s 10.2 [ 14601 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 73636 ] MariaDB v4 [ 149998 ]

            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.