MySQL Bugs: #11639: View + "with rollup" returns strange data

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:	Windows (Windows XP/Linux)
Assigned to:	Igor Babaev	CPU Architecture:	Any

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)

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>

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

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

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

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>