[MDEV-9410] VIEW over a ROLLUP query reports too large columns Created: 2016-01-13  Updated: 2017-05-27  Resolved: 2017-05-27

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.1, 10.2
Fix Version/s: 10.3.1

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-4912 Data type plugin API version 1 Closed
Relates
relates to MDEV-12886 Different data type and default for I... Closed

 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.

 Comments   
Comment by Alexander Barkov [ 2017-05-27 ]

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

Pushed into bb-10.2-ext

Generated at Thu Feb 08 07:34:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.