MySQL Bugs: #36821: HAVING clause returning different result

Bug #36821	HAVING clause returning different result
Submitted:	20 May 2008 16:25	Modified:	18 Jun 2013 9:40
Reporter:	Gian Jones	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.51a/5.1/6.0	OS:	Linux (Redhat Linux Enterprise 4ES X86_64)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any
Tags:	having

Description:
We are migrating our system from 4.0.27 to 5.0.51a and found that the following SQL query behaved differently in 5.0.51a.

### IN MySql v5.0.51a

SELECT COUNT(email.tag) total,done.tag
FROM players AS email
LEFT JOIN players AS done ON email.contact_id=done.contact_id AND email.team_id=done.team_id AND done.tag='SENT_DATE'
WHERE email.team_id=11771 AND email.tag='EMAIL'
GROUP BY email.tag

Running the above query results in:

+-------+------+
| total | tag  |
+-------+------+
|     2 | NULL |
+-------+------+
1 row in set

SELECT COUNT(email.tag) total,done.tag
FROM players AS email
LEFT JOIN players AS done ON email.contact_id=done.contact_id AND email.team_id=done.team_id AND done.tag='SENT_DATE'
WHERE email.team_id=11771 AND email.tag='EMAIL'
GROUP BY email.tag
HAVING done.tag IS NULL

But if the HAVING clause is added then it doesn't return anything. The HAVING clause contains the result of a failed left join so I expected the result to be the same. I think this is a bug? Shouldn't this return the same result as the previous query?

Even though there is another way to write this query, it is the expectation that it should have worked (ie it should have returned the same result as the first query). This is what has baffled me. This also happens in 5.1.22

### IN MySql 4.0.27 both of the above queries return the same result:
+-------+------+
| total | tag  |
+-------+------+
|     2 | NULL |
+-------+------+
1 row in set

How to repeat:
-- ----------------------------
-- Table structure for players
-- ----------------------------
CREATE TABLE `players` (
  `team_id` int(10) unsigned NOT NULL default '0',
  `contact_id` int(11) NOT NULL default '0',
  `tag` varchar(50) NOT NULL default '',
  `value` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`team_id`,`contact_id`,`tag`),
  KEY `team_id` (`team_id`),
  KEY `tag` (`tag`)
) TYPE=MyISAM;

-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `players` (`team_id`, `contact_id`, `tag`, `value`) VALUES ('11771', '666', 'TEAM', 'Arsenal');
INSERT INTO `players` (`team_id`, `contact_id`, `tag`, `value`) VALUES ('11771', '666', 'COUNTRY', 'United Kingdom');
INSERT INTO `players` (`team_id`, `contact_id`, `tag`, `value`) VALUES ('11771', '666', 'EMAIL', 'world@world.co.uk');
INSERT INTO `players` (`team_id`, `contact_id`, `tag`, `value`) VALUES ('11771', '187', 'TEAM', 'Liverpool');
INSERT INTO `players` (`team_id`, `contact_id`, `tag`, `value`) VALUES ('11771', '187', 'COUNTRY', 'United Kingdom');
INSERT INTO `players` (`team_id`, `contact_id`, `tag`, `value`) VALUES ('11771', '187', 'EMAIL', 'globe@globe.co.uk');

I agree with the reporter.  This must be a bug in recent versions!
I think the elaborated example belows shows what goes wrong! 

-- we can simplify the join - 2 of the AND'ed conditions are itself=itself

1)
-- no filter of any kind just to see the returns of the JOIN itself
SELECT email.tag total,count(email.tag) total, done.tag
FROM players AS email
LEFT JOIN players AS done ON done.tag='SENT_DATE'
GROUP BY email.tag;
/* returns
total     total  tag   
-------  ------  ------
COUNTRY       2  (NULL)
EMAIL         2  (NULL)
TEAM          2  (NULL)*/

2)
-- using group by, having and no where 
-- this is the culprit .. last row is lost!
SELECT email.tag total,count(email.tag) total, done.tag
FROM players AS email
LEFT JOIN players AS done ON done.tag='SENT_DATE'
GROUP BY email.tag
HAVING done.tag IS NULL;
/* returns in 5.0.51b

total     total  tag   
-------  ------  ------
COUNTRY       2  (NULL)
EMAIL         2  (NULL)

and in 4.0.27

total     total  tag   
-------  ------  ------
COUNTRY       2  (NULL)
EMAIL         2  (NULL)
TEAM          2  (NULL)
*/

-- the rest is a consequence only! now only showing 5.0.51b

3)
-- using where
SELECT email.tag, done.tag
FROM players AS email
LEFT JOIN players AS done ON done.tag='SENT_DATE'
WHERE email.team_id=11771 AND email.tag='EMAIL';

/*
tag     tag   
------  ------
EMAIL   (NULL)
EMAIL   (NULL)*/

4)
-- using where and group by, but no having
SELECT email.tag total,count(email.tag) total, done.tag
FROM players AS email
LEFT JOIN players AS done ON done.tag='SENT_DATE'
WHERE email.team_id=11771 AND email.tag='EMAIL'
GROUP BY email.tag;
/*
total    total  tag   
------  ------  ------
EMAIL        2  (NULL)*/

5) 
-- the original query (variant)
SELECT COUNT(email.tag) total,done.tag
FROM players AS email
LEFT JOIN players AS done ON email.contact_id=done.contact_id AND
email.team_id=done.team_id AND done.tag='SENT_DATE'
WHERE email.team_id=11771 AND email.tag='EMAIL'
GROUP BY email.tag
HAVING done.tag IS NULL
-- empty set!

Conclusion: when HAVING is used in combination with WHERE the last row of the result in server 5.0.51a is simply lost!

ok .. I mistyped the column alias'es in 1st line of example 2 and 4.  I meant:

SELECT email.tag email_tag ,count(email.tag) total, done.tag done_tag

.. just for completeness ...

Thank you for the bug report. Verified as described.

Server version: 5.0.64-debug Source distribution

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

mysql> SELECT COUNT(email.tag) total,done.tag
    -> FROM players AS email
    -> LEFT JOIN players AS done ON email.contact_id=done.contact_id AND
    -> email.team_id=done.team_id AND done.tag='SENT_DATE'
    -> WHERE email.team_id=11771 AND email.tag='EMAIL'
    -> GROUP BY email.tag;
+-------+------+
| total | tag  |
+-------+------+
|     2 | NULL | 
+-------+------+
1 row in set (0.37 sec)

mysql> SELECT COUNT(email.tag) total,done.tag
    -> FROM players AS email
    -> LEFT JOIN players AS done ON email.contact_id=done.contact_id AND
    -> email.team_id=done.team_id AND done.tag='SENT_DATE'
    -> WHERE email.team_id=11771 AND email.tag='EMAIL'
    -> GROUP BY email.tag
    -> HAVING done.tag IS NULL;
Empty set (0.00 sec)

mysql>

tested with 5.1.68 and 5.6.11, query returns correct result with HAVING. Must
have been fixed looooong ago.