[MDEV-9410] VIEW over a ROLLUP query reports too large columns - Jira

XML

Word

Printable

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.

blocks

MDEV-4912 Data type plugin API version 1

relates to

MDEV-12886 Different data type and default for INT and BIGINT columns in a VIEW for a SELECT with ROLLUP

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