Bug #11639 View + "with rollup" returns strange data
Submitted: 29 Jun 2005 12:53 Modified: 25 Jul 2005 20:35
Reporter: Thomas Belot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.7/5.0.9 Bk Source OS:Microsoft Windows (Windows XP/Linux)
Assigned to: Igor Babaev CPU Architecture:Any

[29 Jun 2005 12:53] Thomas Belot
Description:
When ones try to do a GROUP BY WITH ROLLUP on a view strange things are happening :
(see below for How to repeat)
result with the real table :
'A'
'B'
'C'
NULL

Result with the view :
'B'
'C'
'C'
'C'

How to repeat:
create a table called test with the following values :
"id_test","type"
1,"A"
2,"C"
3,"A"
4,"A"
5,"B"
6,"B"
7,"A"
8,"C"
9,"A"
10,"C"

Then create a view from test as follows :
CREATE VIEW vt AS SELECT * FROM test

Then use the  following query :
SELECT type FROM vt GROUP BY type WITH ROLLUP

(Of course my real query is more complex than that but it's enough to see the bug)
[29 Jun 2005 13:24] Miguel Solorzano
Thank you for the bug report.

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.9-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table test (id_test int, type char(1));
Query OK, 0 rows affected (0.16 sec)

mysql> insert into test values
    -> (1,"A"),(2,"C"),(3,"A"),(4,"A"),(5,"B"),(6,"B"),
    -> (7,"A"),(8,"C"),(9,"A"),(10,"C");
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> CREATE VIEW vt AS SELECT * FROM test;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT type FROM vt GROUP BY type WITH ROLLUP;
+------+
| type |
+------+
| B    |
| C    |
| C    |
| C    |
+------+
4 rows in set (0.00 sec)

mysql> SELECT type FROM test GROUP BY type WITH ROLLUP;
+------+
| type |
+------+
| A    |
| B    |
| C    |
| NULL |
+------+
4 rows in set (0.00 sec)

mysql>
[29 Jun 2005 13:33] Thomas Belot
just to say that in this example it returns the same number of rows (4) but in my *real query* :
- i've got 14 different types and 1 total in the table query 
- I only get 2 *strange* rows in the view query

if it cant help ....

gl hf for bugfix
[1 Jul 2005 10:46] 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/26585
[8 Jul 2005 13:44] Igor Babaev
ChangeSet
  1.1860 05/07/01 03:46:08 igor@rurik.mysql.com +5 -0
  view.result:
    Fixed the results of a test for group_concat.
    After the fix foor bug #11639 the results became
    correct.
  olap.result, olap.test:
    Added a test case for bug #11639.
  sql_select.cc:
    Fixed bug #11639: a wrong result set when using a view
    instead of the underlying table in a rollup query 
    executed through filesort.
    The old code did not take into account that we always
    use an Item_ref object when referring to a view column.
  item.h:
    Fixed bug #11639.
    Now if two Item_ref items ref1 and ref2 refer to the same field
    then ref1->eq(ref2) returns 1.

The fix will appear in 5.0.9
[25 Jul 2005 20:35] Mike Hillyer
Documented in 5.0.9 changelog:

<listitem><para>Incorrect results when using <literal>GROUP BY ... WITH ROLLUP</literal> on a <literal>VIEW</literal>. (Bug #11639)</para></listitem>