Bug #72003 Query fails JOIN in case of use of GROUP BY
Submitted: 11 Mar 2014 7:55 Modified: 11 Mar 2014 11:04
Reporter: Timo Schwiersch Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.6.12 OS:Linux (openSuse 13.1)
Assigned to: CPU Architecture:Any
Tags: GROUP BY, join, null

[11 Mar 2014 7:55] Timo Schwiersch
Description:
I have two tables. One with articles and one with images assigned to the articles (0 to n images per article).

It looks like this:

CREATE TABLE IF NOT EXISTS `article_` (
  `UID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`UID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO `article_` (`UID`) VALUES
(1),(2),(3),(4),(5);

CREATE TABLE IF NOT EXISTS `article_images` (
  `ArticleUID` int(11) unsigned NOT NULL,
  `ImageUID` int(11) unsigned NOT NULL,
  PRIMARY KEY (`ArticleUID`,`ImageUID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `article_images` (`ArticleUID`, `ImageUID`) VALUES
(1, 11),(2, 12),(4, 14),(5, 15);

When I do the following SELECT to get all articles and all assigned images, all is fine:
SELECT a.UID, b.ImageUID FROM article_ as a LEFT JOIN article_images as b ON b.ArticleUID = a.UID;

Result:
1 11
2 12
3 NULL
4 14
5 15

But when I GROUP BY article_.UID to get each article only once (with an assigend image if existing), the JOIN loses the image-connection to article 2.

Query:
SELECT a.UID, b.ImageUID FROM article_ as a LEFT JOIN article_images as b ON b.ArticleUID = a.UID GROUP BY a.UID

Result:
1 11
2 NULL
3 NULL
4 14
5 15

But it is even more obscure. When I now multiply the UID's by 1, it comes back to work:

Query:
SELECT a.UID, b.ImageUID FROM article_ as a LEFT JOIN article_images as b ON b.ArticleUID * 1 = a.UID * 1 GROUP BY a.UID

Result:
1 11
2 12
3 NULL
4 14
5 15

By the way: it is ALWAYS the result above/before the one, where NULL is correct. If I change the sortation of the result, then it is article 4.

Query:
SELECT a.UID, b.ImageUID FROM article_ as a LEFT JOIN article_images as b ON b.ArticleUID = a.UID GROUP BY a.UID ORDER BY a.UID DESC

Result:
5 15
4 NULL
3 NULL
2 12
1 11

There seems to be a serious data error in this MYSQL-version. I tried it with InnoDB and MyISAM. Both storage engines have that error, so I guess it is an error with result handling.

How to repeat:
Repeatable with the instructions above.

Suggested fix:
Proof and correct data handling.
[11 Mar 2014 8:08] Timo Schwiersch
Screenshots

Attachment: sqlerror.png (image/png, text), 28.06 KiB.

[11 Mar 2014 8:41] Hartmut Holzgraefe
looks like a duplicate of http://bugs.mysql.com/bug.php?id=68897

=> fixed since 5.6.13
[11 Mar 2014 11:04] MySQL Verification Team
Please try version 5.6.16. Thanks.