Odtnhj

I am trying to hunt down a bug in a SQL query but can't seem to get to the bottom of it. The query looks like this:

SELECT
 DATE(BT.DateCheckedIn) AS X,
 DAYOFWEEK(BT.DateCheckedIn) AS DayX,
 SUM(IR.QtyCheckedIn) AS C,
 AU.AdminUsername,
 AU.AdminFirstName,
 AU.AdminLastName,
 IF(BTE.ProdLogID IS NULL, 'No', 'Yes') AS Exclude
FROM
 buying_issuesreceived IR
 JOIN buying_transactions BT ON IR.TransactionID = BT.TransactionID
 JOIN adminusers AU ON BT.CheckedInByAdminUserID = AU.AdminUserID
 LEFT JOIN log_production_bt BTE
 ON DATE(BT.DateCheckedIn) = DATE(BTE.ProductionDate)
 AND BTE.ProductionSection = 'wtransactions'
 AND AU.AdminUsername = BTE.ProductionUsername
WHERE
 DATE(BT.DateCheckedIn) BETWEEN DATE '2018-09-24' AND DATE '2018-09-30'
GROUP BY
 DATE(BT.DateCheckedIn),
 AU.AdminUsername

When this query is run, it has about a 50/50 chance of returning the correct data or returning nothing at all. There is no error message. I know, or rather should say, am pretty certain, that the LEFT JOIN is the culprit because when I delete it from the code I stop getting empty tables, but can't, for the life of me, figure out why this query would return inconsistent results in the first place.

This sounds like a MySQL bug. As a workaround, you can use EXISTS with a correlated subquery.

SELECT
 DATE(BT.DateCheckedIn) AS X,
 DAYOFWEEK(BT.DateCheckedIn) AS DayX,
 SUM(IR.QtyCheckedIn) AS C,
 AU.AdminUsername,
 AU.AdminFirstName,
 AU.AdminLastName, 
 IF(EXISTS(
 SELECT 1
 FROM log_production_bt BTE
 WHERE 
 DATE(BT.DateCheckedIn) = DATE(BTE.ProductionDate)
 AND BTE.ProductionSection = 'wtransactions'
 AND AU.AdminUsername = BTE.ProductionUsername), 'NO', 'YES') AS Exclude
FROM
 buying_issuesreceived IR
 JOIN buying_transactions BT ON IR.TransactionID = BT.TransactionID
 JOIN adminusers AU ON BT.CheckedInByAdminUserID = AU.AdminUserID
WHERE
 DATE(BT.DateCheckedIn) BETWEEN DATE '2018-09-24' AND DATE '2018-09-30'
GROUP BY
 DATE(BT.DateCheckedIn),
 AU.AdminUsername