MySQL Bugs: #30175: GROUP BY returns wrong records with subquery condition NOT EXISTS

Bug #30175	GROUP BY returns wrong records with subquery condition NOT EXISTS
Submitted:	1 Aug 2007 9:39	Modified:	1 Aug 2007 13:43
Reporter:	Davor Strehar	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.40	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	distinct, GROUP BY, not exists, query, recordset, SQL, subselect

Description:
GROUP BY does not work as expected with subquery condition NOT EXISTS and thus returning wrong recordset. It looks like the records which are matched with NOT EXISTS condition are not being dismissed as they should be...

How to repeat:
Create tables:
CREATE TABLE `aa` (
  `aa_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY  (`aa_id`)
);
CREATE TABLE `bb` (
  `bb_id` int(11) NOT NULL,
  `aa_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`bb_id`),
  KEY `aa_id` (`aa_id`)
);
CREATE TABLE `cc` (
  `cc_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY  (`cc_id`)
);

Insert test data:
INSERT INTO `aa` (`aa_id`, `num`) VALUES (1, 1),
(2, 2);
INSERT INTO `bb` (`bb_id`, `aa_id`, `name`) VALUES (1, 1, 'bb1'),
(2, 1, 'bb2'),
(3, 1, 'bb3'),
(4, 2, 'bb4');
INSERT INTO `cc` (`cc_id`, `num`) VALUES (1, 2),
(2, 3);

Simple query with NOT EXISTS condition:
SELECT aa.aa_id, bb.name FROM aa, bb
WHERE aa.aa_id = bb.aa_id AND
NOT EXISTS (SELECT cc_id
FROM cc WHERE cc.num = aa.num);
+-------+------+
| aa_id | name |
+-------+------+
|     1 | bb1  |
|     1 | bb2  |
|     1 | bb3  |
+-------+------+
3 rows in set (0.00 sec)

Same query with GROUP BY:
SELECT aa.aa_id FROM aa, bb
WHERE aa.aa_id = bb.aa_id AND
NOT EXISTS (SELECT cc_id
FROM cc WHERE cc.num = aa.num)
GROUP BY aa.aa_id;
+-------+
| aa_id |
+-------+
|     1 |
|     2 |
+-------+
2 rows in set (0.00 sec)

DISTINCT works as expected:
SELECT DISTINCT aa.aa_id FROM aa, bb
WHERE aa.aa_id = bb.aa_id AND
NOT EXISTS (SELECT cc_id
FROM cc WHERE cc.num = aa.num);
+-------+
| aa_id |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

Thank you for the report.

I can not repeat described behaviour with nor with current development sources, neither with version 5.0.44 although bug is repeatable with version 5.0.42.

So I marked report as "Can't repeat".

Consider to upgrade to current verison.

Upgraded to 5.0.44 and it works as expected now. Thanks!