[MDEV-28536] Incorrect sequence in view column processing created as select with ROLLUP and repeated columns in GROUP BY - Jira

XML

Word

Printable

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.6, 10.3(EOL), 10.4(EOL), 10.5(EOL), 10.7(EOL), 10.8(EOL), 10.9(EOL)
Fix Version/s: 10.11, 11.4, 11.8
Component/s: Optimizer, Views
Labels:
- view-protocol

If select for creating view contains repeated column in GROUP BY and ROLLUP data in repeated columns are swapped.

Test:

CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) NOT NULL);

INSERT INTO t1 VALUES (1, 1);

INSERT INTO t1 VALUES (1, 2);

SELECT a, b,  a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP;

CREATE VIEW v1 as SELECT a, b,  a AS c,  COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP;

SELECT * FROM v1;

DROP VIEW v1;

DROP TABLE t1;

Actual result:

SELECT a, b,  a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP;

a	b	c	count

1	1	1	1

1	1	NULL	1

1	2	1	1

1	2	NULL	1

1	NULL	NULL	2

NULL	NULL	NULL	2

CREATE VIEW v1 as SELECT a, b,  a AS c,  COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP;

SELECT * FROM v1;

a	b	c	count

1	1	1	1

NULL	1	1	1

1	2	1	1

NULL	2	1	1

NULL	NULL	1	2

NULL	NULL	1	2

Expected result:

SELECT a, b,  a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP;

a	b	c	count

1	1	1	1

1	1	NULL	1

1	2	1	1

1	2	NULL	1

1	NULL	NULL	2

NULL	NULL	NULL	2

CREATE VIEW v1 as SELECT a, b,  a AS c,  COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP;

SELECT * FROM v1;

a	b	c	count

1	1	1	1

1	1	NULL	1

1	2	1	1

1	2	NULL	1

1	NULL	NULL	2

NULL	NULL	NULL	2

is part of

MDEV-27691 make working view-protocol

relates to

MDEV-16612 SP variables do not return expected NULL in summary rows in ROLLUP queries

MDEV-19039 Wrong result from query, using window function and GROUP BY .. WITH ROLLUP

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