| Bug #11543 | Error in GROUP BY .. WITH ROLLUP | ||
|---|---|---|---|
| Submitted: | 24 Jun 2005 9:08 | Modified: | 25 Jul 2005 20:38 |
| Reporter: | Wiebren Braakman | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 4.1.11-Debian_0.dotdeb.0-log | OS: | Linux (Linux - Debian) |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[24 Jun 2005 9:42]
Alexander Keremidarski
Verified against 4.1 fro BK tree ChangeSet@1.2260.50.1, 2005-06-02 21:14:14+02:00
[1 Jul 2005 14:32]
Igor Babaev
This bug was investigated by Alexander Ivanov. He also prepared a fix for this bug. Here's his report: GROUP BY ... WITH ROLLUP in some cases returns incorrect result. This can be seen from the following examples mysql> CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) NOT NULL, c INT(10) NOT NULL); Query OK, 0 rows affected (0.12 sec) mysql> INSERT INTO t1 VALUES (1, 1, 1); Query OK, 1 row affected (0.02 sec) mysql> SELECT a, b, a AS s, COUNT(*) AS count FROM t1 GROUP BY a, b, s WITH ROLLUP; +-----+-----+-----+-------+ | a | b | s | count | +-----+-----+-----+-------+ | 1 | 1 | 1 | 1 | |NULL | 1 |NULL | 1 | |NULL |NULL |NULL | 1 | |NULL |NULL |NULL | 1 | +-----+-----+-----+-------+ 4 rows in set (0.00 sec) The correct result should be: +-----+-----+-----+-------+ | a | b | s | count | +-----+-----+-----+-------+ | 1 | 1 | 1 | 1 | | 1 | 1 |NULL | 1 | | 1 |NULL |NULL | 1 | |NULL |NULL |NULL | 1 | +-----+-----+-----+-------+ mysql> INSERT INTO t1 VALUES (1, 2, 2); Query OK, 1 row affected (0.00 sec) mysql> SELECT a, b, a AS s, COUNT(*) AS count FROM t1 GROUP BY a, b, s WITH ROLLUP; +-----+-----+-----+-------+ | a | b | s | count | +-----+-----+-----+-------+ | 1 | 1 | 1 | 1 | |NULL | 1 |NULL | 1 | | 1 | 2 | 1 | 1 | |NULL | 2 |NULL | 1 | |NULL |NULL |NULL | 2 | |NULL |NULL |NULL | 2 | +-----+-----+-----+-------+ 6 rows in set (0.02 sec) The correct result should be: +-----+-----+-----+-------+ | a | b | s | 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 | +-----+-----+-----+-------+ The cause of the bug is incorrect behavior of the JOIN::rollup_make_fields() function in the case when the query contains an "... AS ..." field. Namely, when identifying a selected field with a group_by field the function uses the check (1) item->eq(*group_tmp->item, 0) which says (for example above) that the item for the field 'a' and the one for the field 'a AS s' are "equal". That is why in the output table the fields 'a' and 's' are either both NULL or both not NULL. In correct version, the identifying must be based on the check (2) item == *group_tmp->item (Note that in version 5.0 the check (2) is used in this case). The function is corrected. mysql> CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) NOT NULL, c INT(10) NOT NULL); Query OK, 0 rows affected (0.06 sec) mysql> INSERT INTO t1 VALUES (1, 1, 1); Query OK, 1 row affected (0.03 sec) mysql> SELECT a, b, a AS s, COUNT(*) AS count FROM t1 GROUP BY a, b, s WITH ROLLUP; +-----+-----+-----+-------+ | a | b | s | count | +-----+-----+-----+-------+ | 1 | 1 | 1 | 1 | | 1 | 1 |NULL | 1 | | 1 |NULL |NULL | 1 | |NULL |NULL |NULL | 1 | +-----+-----+-----+-------+ 4 rows in set (0.03 sec) mysql> INSERT INTO t1 VALUES (1, 2, 2); Query OK, 1 row affected (0.00 sec) mysql> SELECT a, b, a AS s, COUNT(*) AS count FROM t1 GROUP BY a, b, s WITH ROLL UP; +-----+-----+-----+-------+ | a | b | s | 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 | +-----+-----+-----+-------+ 6 rows in set (0.00 sec)
[1 Jul 2005 14:40]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/26601
[8 Jul 2005 13:41]
Igor Babaev
The patch to fix this bug was prepared by Alexander Ivanov.
ChangeSet
1.2329 05/07/01 07:40:22 igor@rurik.mysql.com +3 -0
olap.result, olap.test:
Added a test case for bug #11543.
sql_select.cc:
Fixed bug #11543.
A ROLLUP query could return a wrong result set when
its GROUP BY clause contained references to the same
column.
The fix will appear in 4.0.13 (in 5.0 the code was correct).
[25 Jul 2005 20:38]
Mike Hillyer
Documented in 4.1.13 changelog: <listitem><para>A <literal>ROLLUP</literal> query could return a wrong result set when its <literal>GROUP BY</literal> clause contained references to the same column. (Bug #11543)</para></listitem>
Description: I think there is a error in if you do a "GROUP BY .. WITH ROLLUP" if the same collum is used twice. How to repeat: -- The test table CREATE TABLE `test` ( `a` INT( 10 ) NOT NULL , `b` INT( 10 ) NOT NULL , `c` INT( 10 ) NOT NULL ) TYPE = MYISAM ; -- Some test data INSERT INTO `test` ( `a` , `b` , `c` ) VALUES ('1', '1', '1'); INSERT INTO `test` ( `a` , `b` , `c` ) VALUES ('1', '2', '2'); INSERT INTO `test` ( `a` , `b` , `c` ) VALUES ('1', '2', '3'); INSERT INTO `test` ( `a` , `b` , `c` ) VALUES ('2', '1', '4'); INSERT INTO `test` ( `a` , `b` , `c` ) VALUES ('2', '2', '5'); INSERT INTO `test` ( `a` , `b` , `c` ) VALUES ('2', '3', '6'); INSERT INTO `test` ( `a` , `b` , `c` ) VALUES ('2', '4', '7'); INSERT INTO `test` ( `a` , `b` , `c` ) VALUES ('3', '1', '8'); INSERT INTO `test` ( `a` , `b` , `c` ) VALUES ('3', '2', '9'); INSERT INTO `test` ( `a` , `b` , `c` ) VALUES ('3', '4', '10'); -- The query SELECT `a` , `b` , `a` , COUNT(*) AS 'count' FROM `test` GROUP BY `a` , `b` , `a` WITH ROLLUP This should return the row count for - unique combinations of `a` and `b` - unique combinations of `a` - unique combinations of `b` - all rows This sould be te output in that case: +-----+-----+-----+-------+ | a | b | a | count | +-----+-----+-----+-------+ | 1 | 1 | 1 | 1 | |NULL | 1 | 1 | 1 | | 1 | 2 | 1 | 3 | |NULL | 2 | 1 | 3 | |NULL |NULL | 1 | 4 | | 2 | 1 | 2 | 1 | |NULL | 1 | 2 | 1 | | 2 | 2 | 2 | 1 | |NULL | 2 | 2 | 1 | | 2 | 3 | 2 | 1 | |NULL | 3 | 2 | 1 | | 2 | 4 | 2 | 1 | |NULL | 4 | 2 | 1 | |NULL |NULL | 2 | 4 | | 3 | 1 | 3 | 1 | |NULL | 1 | 3 | 1 | | 3 | 2 | 3 | 1 | |NULL | 2 | 3 | 1 | | 3 | 4 | 3 | 1 | |NULL | 4 | 3 | 1 | |NULL |NULL | 3 | 3 | |NULL |NULL |NULL | 11 | +-----+-----+-----+-------+ But the output is this: (the last row is different) +-----+-----+-----+-------+ | a | b | a | count | +-----+-----+-----+-------+ | 1 | 1 | 1 | 1 | |NULL | 1 | 1 | 1 | | 1 | 2 | 1 | 3 | |NULL | 2 | 1 | 3 | |NULL |NULL | 1 | 4 | | 2 | 1 | 2 | 1 | |NULL | 1 | 2 | 1 | | 2 | 2 | 2 | 1 | |NULL | 2 | 2 | 1 | | 2 | 3 | 2 | 1 | |NULL | 3 | 2 | 1 | | 2 | 4 | 2 | 1 | |NULL | 4 | 2 | 1 | |NULL |NULL | 2 | 4 | | 3 | 1 | 3 | 1 | |NULL | 1 | 3 | 1 | | 3 | 2 | 3 | 1 | |NULL | 2 | 3 | 1 | | 3 | 4 | 3 | 1 | |NULL | 4 | 3 | 1 | |NULL |NULL | 3 | 3 | |NULL |NULL | 3 | 11 | +-----+-----+-----+-------+ Suggested fix: The last row sould be: +-----+-----+-----+-------+ | a | b | a | count | +-----+-----+-----+-------+ |NULL |NULL |NULL | 11 | +-----+-----+-----+-------+