Bug #46465 When IF is used with ROLLUP incorrect data is returned
Submitted: 30 Jul 2009 3:34 Modified: 10 Feb 2018 17:54
Reporter: Chris Hamono Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0.32, 5.1.38 OS:Linux (debian-etch)
Assigned to: CPU Architecture:Any
Tags: IF, with rollup

[30 Jul 2009 3:34] Chris Hamono 
Description:
While building a timesheet application I wished to create a query that would display the current hours per day and the total hours for the fortnight to date

I also wanted to 'flag' the total (rollup) so I used an if statement
IF(`day`,`date`,'total') as date2

Please note the query shown is substantially different from the query I intend to run I chopped out a lot it to test it

How to repeat:
// create the database table
CREATE TABLE IF NOT EXISTS `timesheets` (
  `id` int(11) NOT NULL auto_increment,
  `employee_id` int(11) NOT NULL default '0',
  `manager_id` int(11) NOT NULL default '0',
  `cost_centre_id` int(11) NOT NULL default '0',
  `created` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `variation` varchar(60) NOT NULL default '',
  `period_ending` date NOT NULL default '0000-00-00',
  `rostered_hours` decimal(6,2) NOT NULL default '0.00',
  `hours` decimal(6,2) NOT NULL default '0.00',
  `flexitime` decimal(6,2) NOT NULL default '0.00',
  `date` date NOT NULL default '0000-00-00',
  `day` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `cost_centre_id` (`cost_centre_id`),
  KEY `employee_id` (`employee_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

// fill it with some data
INSERT INTO `timesheets` (`id`, `employee_id`, `manager_id`, `cost_centre_id`, `created`, `variation`, `period_ending`, `rostered_hours`, `hours`, `flexitime`, `date`, `day`) VALUES
(1, 2, 1052, 22, '2009-07-29 15:17:34', '', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-06', 3),
(2, 3, 1052, 22, '2009-07-29 15:17:34', '', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-06', 3),
(3, 2, 1052, 22, '2009-07-29 15:17:39', '', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-07', 4),
(4, 3, 1052, 22, '2009-07-29 15:17:39', '', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-07', 4),
(5, 2, 1052, 22, '2009-07-29 15:17:43', '', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-08', 5),
(6, 3, 1052, 22, '2009-07-29 15:17:43', '', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-08', 5),
(7, 2, 1052, 22, '2009-07-29 15:17:47', '', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-09', 6),
(8, 3, 1052, 22, '2009-07-29 15:17:47', '', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-09', 6),
(9, 2, 1052, 22, '2009-07-29 15:19:33', '', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-09', 6),
(10, 3, 1052, 22, '2009-07-29 15:19:33', '', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-09', 6),
(11, 2, 1052, 22, '2009-07-29 15:46:38', 'LSL', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-10', 7),
(12, 3, 1052, 22, '2009-07-29 15:55:34', '', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-10', 7),
(13, 2, 1052, 22, '2009-07-29 15:55:42', '', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-13', 10),
(14, 3, 1052, 22, '2009-07-29 15:55:42', '', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-13', 10),
(15, 2, 1052, 22, '2009-07-29 15:55:50', '', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-14', 11),
(16, 3, 1052, 22, '2009-07-29 15:55:50', '', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-14', 11),
(17, 2, 1052, 22, '2009-07-29 15:55:54', '', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-15', 12),
(18, 3, 1052, 22, '2009-07-29 15:55:54', '', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-15', 12),
(19, 2, 1052, 22, '2009-07-29 15:55:58', '', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-16', 13),
(20, 3, 1052, 22, '2009-07-29 15:55:58', '', '2009-07-17', '7.50', '7.50', '0.00', '2009-07-16', 13);

// run this query and take note of the date and date2 fields
SELECT `date`,IF(`day`,`date`,'total') as date2,SUM(`hours`) FROM `timesheets` WHERE `cost_centre_id`=22 GROUP BY `day`

// add WITH ROLLUP and run again
SELECT `date`,IF(`day`,`date`,'total') as date2,SUM(`hours`),`day` FROM `timesheets` WHERE `cost_centre_id`=22 GROUP BY `day` WITH ROLLUP

notice how in the second run the two dates are mismatched the date2 field appears to have the next date from the result set except the last entry

my bad results in case you see something different...
+------------+------------+--------------+-----+
| date       | date2      | SUM(`hours`) | day |
+------------+------------+--------------+-----+
| 2009-07-06 | 2009-07-07 |        15.00 |   3 |
| 2009-07-07 | 2009-07-08 |        15.00 |   4 |
| 2009-07-08 | 2009-07-09 |        15.00 |   5 |
| 2009-07-09 | 2009-07-10 |        30.00 |   6 |
| 2009-07-10 | 2009-07-13 |        15.00 |   7 |
| 2009-07-13 | 2009-07-14 |        15.00 |  10 |
| 2009-07-14 | 2009-07-15 |        15.00 |  11 |
| 2009-07-15 | 2009-07-16 |        15.00 |  12 |
| 2009-07-16 | 2009-07-16 |        15.00 |  13 |
| 2009-07-16 | total      |       150.00 | NULL |
+------------+------------+--------------+-----+
10 rows in set (0.00 sec)                        

expected results (no rollup)
+------------+------------+--------------+-----+
| date       | date2      | SUM(`hours`) | day |
+------------+------------+--------------+-----+
| 2009-07-06 | 2009-07-06 |        15.00 |   3 |
| 2009-07-07 | 2009-07-07 |        15.00 |   4 |
| 2009-07-08 | 2009-07-08 |        15.00 |   5 |
| 2009-07-09 | 2009-07-09 |        30.00 |   6 |
| 2009-07-10 | 2009-07-10 |        15.00 |   7 |
| 2009-07-13 | 2009-07-13 |        15.00 |  10 |
| 2009-07-14 | 2009-07-14 |        15.00 |  11 |
| 2009-07-15 | 2009-07-15 |        15.00 |  12 |
| 2009-07-16 | 2009-07-16 |        15.00 |  13 |
+------------+------------+--------------+-----+
9 rows in set (0.00 sec)     

Suggested fix:
Nope no idea
[30 Jul 2009 4:26] Valeriy Kravchuk 
Thank you for the problem report. Verified just as described with latest 5.1.38 from bzr:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.38-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT `date`,IF(`day`,`date`,'total') as date2,SUM(`hours`) FROM `timesheets` WHERE
    -> `cost_centre_id`=22 GROUP BY `day`
    -> ;
+------------+------------+--------------+
| date       | date2      | SUM(`hours`) |
+------------+------------+--------------+
| 2009-07-06 | 2009-07-06 |        15.00 | 
| 2009-07-07 | 2009-07-07 |        15.00 | 
| 2009-07-08 | 2009-07-08 |        15.00 | 
| 2009-07-09 | 2009-07-09 |        30.00 | 
| 2009-07-10 | 2009-07-10 |        15.00 | 
| 2009-07-13 | 2009-07-13 |        15.00 | 
| 2009-07-14 | 2009-07-14 |        15.00 | 
| 2009-07-15 | 2009-07-15 |        15.00 | 
| 2009-07-16 | 2009-07-16 |        15.00 | 
+------------+------------+--------------+
9 rows in set (0.05 sec)

mysql> SELECT `date`,IF(`day`,`date`,'total') as date2,SUM(`hours`) FROM `timesheets` WHERE `cost_centre_id`=22 GROUP BY `day` WITH ROLLUP;;
+------------+------------+--------------+
| date       | date2      | SUM(`hours`) |
+------------+------------+--------------+
| 2009-07-06 | 2009-07-07 |        15.00 | 
| 2009-07-07 | 2009-07-08 |        15.00 | 
| 2009-07-08 | 2009-07-09 |        15.00 | 
| 2009-07-09 | 2009-07-10 |        30.00 | 
| 2009-07-10 | 2009-07-13 |        15.00 | 
| 2009-07-13 | 2009-07-14 |        15.00 | 
| 2009-07-14 | 2009-07-15 |        15.00 | 
| 2009-07-15 | 2009-07-16 |        15.00 | 
| 2009-07-16 | 2009-07-16 |        15.00 | 
| 2009-07-16 | total      |       150.00 | 
+------------+------------+--------------+
10 rows in set (0.02 sec)
[30 Jul 2009 4:54] Giuseppe Maxia 
It doesn't look like a bug to me.
The query is grouping by *day*. If I add non aggregated columns, their value is undefined. Hence, in different runs, I can get different results.
[30 Jul 2009 6:08] Chris Hamono 
Thanks Giuseppe.

I can confirm that if I use the aggregate function MAX() to select the date the function works as expected.

using min and max...
SELECT MAX(`date`),IF(`day`,MIN(`date`),'total') as date2,SUM(`hours`),`day` FROM `timesheets` WHERE `cost_centre_id`=22 GROUP BY `day` WITH ROLLUP

As the `date` has the same value within the aggregated set I assumed it should return that value.
[30 Jul 2009 6:14] Chris Hamono 
I would just like to point out that without the "WITH ROLLUP" the results are as expected. ie: `date` returns the same value inside and outside of the IF statement.
[10 Feb 2018 17:54] Roy Lyseng 
Posted by developer:
 
Original test case was using non-deterministic GROUP BY operation.
Apparently not a bug.
Please reopen if you have a test case that uses SQL mode ONLY_FULL_GROUP_BY.