MySQL Bugs: #28950: Using EXISTS and a GROUP BY incorrectly yields no results

Bug #28950	Using EXISTS and a GROUP BY incorrectly yields no results
Submitted:	7 Jun 2007 15:22	Modified:	7 Jun 2007 18:11
Reporter:	Arjen lastname	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.40, 5.0.41	OS:	Linux (x86, x86-64)
Assigned to:		CPU Architecture:	Any

Description:
I've a query that makes MySQL return no results at all in MySQL 5.0.40 on x86 (gentoo version) and 5.0.41 on x86-64 (debian version), while the same query does yield 27 results on two different 5.0.38's.

The query can probably be more compact, but I don't know exactly which fields I can leave out:

This one fails:
SELECT SQL_CALC_FOUND_ROWS pr.ID, pr.Naam, pr.InfoID, pr.productCatId,
                       COUNT(prel.ProduktID) AS Count, MAX(m.Time) AS Time, pc.Naam AS Category
                   FROM
                           testprod AS pr
                           JOIN testprel AS prel ON prel.Tabel = 'm'
                                           AND prel.ProduktID = pr.ID
                           JOIN testmeuk AS m ON m.ID = prel.TabelID
                           JOIN testprodcat AS pc ON pc.ID = pr.productCatId
                   WHERE pr.productCatId IN (326, 509, 604, 510, 511)
                       AND pr.Meuk = 1
                       AND m.Embargo = 'N'
               AND EXISTS (SELECT 1 FROM testicc AS c
                           WHERE c.item_type = 'm'
                               AND c.item_id = m.ID
                               AND c.category_id IN (1)
                               )
                   GROUP BY pr.ID
                   ORDER BY pr.Naam ASC
                   LIMIT 0,100
;

When I leave the c.item_id = m.ID out, it yields results, even though (in this sample case all) some of the records should match the exists-subquery.

Rewriting it to these two variants yields 27 results again:
SELECT SQL_CALC_FOUND_ROWS ID, Naam, InfoID, productCatId,
                       COUNT(pCount) AS Count, MAX(Time) AS Time, Category
FROM
(
    SELECT pr.ID, pr.Naam, pr.InfoID, pr.productCatId, prel.ProduktID AS pCount, m.Time AS Time, pc.Naam AS Category
                   FROM
                           testprod AS pr
                           JOIN testprel AS prel ON prel.Tabel = 'm'
                                           AND prel.ProduktID = pr.ID
                           JOIN testmeuk AS m ON m.ID = prel.TabelID
                           JOIN testprodcat AS pc ON pc.ID = pr.productCatId
                   WHERE pr.productCatId IN (326, 509, 604, 510, 511)
                       AND pr.Meuk = 1
                       AND m.Embargo = 'N'

               AND EXISTS (SELECT 1 FROM testicc AS c
                           WHERE c.item_type = 'm'
                               AND c.item_id = m.ID
                               AND c.category_id IN (1)
                               )
) as foo
GROUP BY ID
ORDER BY Naam ASC
LIMIT 0,100;

Or using IN rather than EXISTS:
SELECT SQL_CALC_FOUND_ROWS pr.ID, pr.Naam, pr.InfoID, pr.productCatId,
                       COUNT(prel.ProduktID) AS Count, MAX(m.Time) AS Time, pc.Naam AS Category
                   FROM
                           testprod AS pr
                           JOIN testprel AS prel ON prel.Tabel = 'm'
                                           AND prel.ProduktID = pr.ID
                           JOIN testmeuk AS m ON m.ID = prel.TabelID
                           JOIN testprodcat AS pc ON pc.ID = pr.productCatId
                   WHERE pr.productCatId IN (326, 509, 604, 510, 511)
                       AND pr.Meuk = 1
                       AND m.Embargo = 'N'
               AND m.ID IN (SELECT c.item_id FROM testicc AS c
                           WHERE c.item_type = 'm'
                               AND c.category_id IN (1)
                               )
                   GROUP BY pr.ID
                   ORDER BY pr.Naam ASC
                   LIMIT 0,100
;

It may be the type-mismatch between c.item_id (signed integer) and m.ID (unsigned mediumint) but adding a cast there doesn't do anything to the results apart from increasing the query time.

How to repeat:
Insert the attached table-structures and data and execute the above query on MySQL 5.0.40 or 5.0.41 you should get 27 results instead of none, like from the two equivalent queries and like 5.0.38 does.

The original tables obviously have more and more interesting data, but this small dataset was enough to trigger the behaviour, it may be possible to leave even more out of the query (like the table testprodcat).

Thank you for a problem report. I was not able to repeat it, though, with latest 5.0.44-BK:

mysql> SELECT SQL_CALC_FOUND_ROWS pr.ID, pr.Naam, pr.InfoID, pr.productCatId,
    ->                        COUNT(prel.ProduktID) AS Count, MAX(m.Time) AS Time, pc.Naam AS
    -> Category
    ->                    FROM
    ->                            testprod AS pr
    ->                            JOIN testprel AS prel ON prel.Tabel = 'm'
    ->                                            AND prel.ProduktID = pr.ID
    ->                               JOIN testmeuk AS m ON m.ID = prel.TabelID
    ->                            JOIN testprodcat AS pc ON pc.ID = pr.productCatId
    ->                    WHERE pr.productCatId IN (326, 509, 604, 510, 511)
    ->                        AND pr.Meuk = 1
    ->                        AND m.Embargo = 'N'
    ->                AND EXISTS (SELECT 1 FROM testicc AS c
    ->                            WHERE c.item_type = 'm'
    ->                                AND c.item_id = m.ID
    ->                                AND c.category_id IN (1)
    ->                                )
    ->                    GROUP BY pr.ID
    ->                    ORDER BY pr.Naam ASC
    ->                    LIMIT 0,100
    -> ;
+--------+----------------------------------------------------------------+-----
---+--------------+-------+------+------------------+
| ID     | Naam                                                           | Info
ID | productCatId | Count | Time | Category         |
+--------+----------------------------------------------------------------+-----
---+--------------+-------+------+------------------+
| 116852 | AOpen XC Cube EX18 (Socket A, nForce2-GT, AGP, Zilver)         |
 0 |          509 |     1 |    0 | (HT)PC Barebones |
...
| 111025 | Tyan Transport TA26 B2882T26U8H-R (2U, 2x S940, 16GB, 8x U320) |
 0 |          510 |     2 |    0 | Server Barebones |
+--------+----------------------------------------------------------------+-----
---+--------------+-------+------+------------------+
27 rows in set (0.09 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.44    |
+-----------+
1 row in set (0.00 sec)