MySQL Bugs: #4767: WITH ROLLUP fails on left-join

Bug #4767	WITH ROLLUP fails on left-join
Submitted:	27 Jul 2004 15:46	Modified:	23 Aug 2004 22:06
Reporter:	Tom Cunningham	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.2-alpha-standard	OS:	Linux (SuSe)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
Doing a 'with rollup' on a left-join query, esp. where column is 'not null', alters the original results.

How to repeat:
  DROP TABLE IF EXISTS user_day;
CREATE TABLE user_day
       (user_id INT NOT NULL,
       date DATE NOT NULL,
       UNIQUE INDEX user_date (user_id, date) );

INSERT INTO user_day
VALUES (1, '2004-06-06' ),
       (1, '2004-06-07' ),
       (2, '2004-06-06' );

SELECT
       d.date AS day,
       COUNT(d.user_id) as sample,
       COUNT(next_day.user_id) AS not_cancelled

  FROM user_day d
  LEFT JOIN user_day next_day ON next_day.user_id=d.user_id AND next_day.date= DATE_ADD( d.date, interval 1 day )

 GROUP BY day
  WITH ROLLUP;

-- try it with & without rollup, it returns different values. If you remove the 'not null' from the table definition for user_id, then it works OK again.

Verified on 4.1.3

ROLLUP Query result: 

+------------+--------+---------------+
| day        | sample | not_cancelled |
+------------+--------+---------------+
| 2004-06-06 |      2 |             2 |
| 2004-06-07 |      1 |             1 |
| NULL       |      3 |             3 |
+------------+--------+---------------+

expected result:

+------------+--------+---------------+
| day        | sample | not_cancelled |
+------------+--------+---------------+
| 2004-06-06 |      2 |             1 |
| 2004-06-07 |      1 |             0 |
| NULL       |      3 |             1 |
+------------+--------+---------------+

note the different vaules in the not_cancelled column

EXPECT for wrong result:

+----+-------------+----------+--------+---------------+-----------+---------+---------------------+------+----------------------------------------------+
| id | select_type | table    | type   | possible_keys | key       | key_len | ref                 | rows | Extra                                        |
+----+-------------+----------+--------+---------------+-----------+---------+---------------------+------+----------------------------------------------+
|  1 | SIMPLE      | d        | index  | NULL          | user_date |       7 | NULL                |    3 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | next_day | eq_ref | user_date     | user_date |       7 | test.d.user_id,func |    1 | Using index                                  |
+----+-------------+----------+--------+---------------+-----------+---------+---------------------+------+----------------------------------------------+

EXPECT for expected result:

+----+-------------+----------+-------+---------------+-----------+---------+---------------------+------+----------------------------------------------+
| id | select_type | table    | type  | possible_keys | key       | key_len | ref                 | rows | Extra                                        |
+----+-------------+----------+-------+---------------+-----------+---------+---------------------+------+----------------------------------------------+
|  1 | SIMPLE      | d        | index | NULL          | user_date |       8 | NULL                |    3 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | next_day | ref   | user_date     | user_date |       8 | test.d.user_id,func |    2 | Using index                                  |
+----+-------------+----------+-------+---------------+-----------+---------+---------------------+------+----------------------------------------------+

The key difference here is that 'type' for the 2nd table is 'eq_ref' for the wrong result and 'ref' for the expected one.

Although the problem fixed, the patch looks like a hack.
The test case was added to olap.test.

hangeSet
  1.1950 04/08/12 00:02:29 igor@rurik.mysql.com +4 -0
  olap.test, olap.result:
    Added test case for bug #4767.
  item_sum.cc:
    Added a correct setting of the maybe_null flag for a copy of 
    an Item_sum object where the argument was a field of an inner table
    in an outer join read from a temporary table.
    It's part of the fix for bug #4767.
  sql_select.cc:
    Made change_refs_to_tmp_fields work correctly
    for test case of bug #4767 where Item_sum::get_tmp_table_item
    failed to build a correct copy of an Item_sum object referring
    to a field in a temporary table.
    It looks like a hack yet.

see also

ChangeSet@1.1962.2.1, 2004-08-23 10:53:53+02:00, serg@serg.mylan
   better for for bug#4767

Hi my name is Julio.

I would like to know what was the solution or the patch. Does anybody can tell send me information or tell me where I can find it.

Best regards,
Julio C Escamilla