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.
