MySQL Forums
Forum List  »  InnoDB

Bug in HAVING COUNT?
Posted by: John Viescas
Date: June 07, 2016 04:56AM

Using MySQL Community 5.6.22 and InnoDB.
Trying to run SQL that looks like:
CREATE VIEW CustStyles AS
SELECT C.CustomerID, C.CustFirstName,
C.CustLastName, MS.StyleName
FROM Customers AS C INNER JOIN Musical_Preferences AS MP
ON C.CustomerID = MP.CustomerID
INNER JOIN Musical_Styles AS MS
ON MP.StyleID = MS.StyleID;

CREATE VIEW EntStyles AS
SELECT E.EntertainerID, E.EntStageName, MS.StyleName
FROM Entertainers AS E INNER JOIN Entertainer_Styles AS ES
ON E.EntertainerID = ES.EntertainerID
INNER JOIN Musical_Styles AS MS
ON ES.StyleID = MS.StyleID;

SELECT CustStyles.CustomerID, CustStyles.CustFirstName,
CustStyles.CustLastName, EntStyles.EntStageName
FROM CustStyles INNER JOIN EntStyles
ON CustStyles.StyleName = EntStyles.StyleName
GROUP BY CustStyles.CustomerID, CustStyles.CustFirstName,
CustStyles.CustLastName, EntStyles.EntStageName
HAVING COUNT(EntStyles.StyleName) =
(SELECT COUNT(StyleName)
FROM CustStyles AS CS1
WHERE CS1.CustomerID = CustStyles.CustomerID)
ORDER BY CustStyles.CustomerID;

The answer should be eight exact matches of customer preferences to entertainers who play those styles, but I get only ONE row.

If I try this:
SELECT CustStyles.CustomerID, CustStyles.CustFirstName,
CustStyles.CustLastName, EntStyles.EntStageName, COUNT(EntStyles.StyleName) AS EntCount, (SELECT COUNT(StyleName)
FROM CustStyles AS CS2
WHERE CS2.CustomerID = CustStyles.CustomerID) AS CustCount
FROM CustStyles INNER JOIN EntStyles
ON CustStyles.StyleName = EntStyles.StyleName
GROUP BY CustStyles.CustomerID, CustStyles.CustFirstName,
CustStyles.CustLastName, EntStyles.EntStageName;

I get 51 rows, eight of which show a match on EntCount and CustCount. The only row returned when I add the HAVING clause is one that matches on a customer who has three styles defined. All other customers who should get a match have only two each.

BTW, the original query works just fine in Microsoft Access, SQL Server, DB2, Oracle, and PostgreSQL. Is this a bug in MySQL?

Options: ReplyQuote


Subject
Views
Written By
Posted
Bug in HAVING COUNT?
1461
June 07, 2016 04:56AM
515
June 07, 2016 05:19AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.