Description:
I have a query that is missing results when I add a LEFT JOIN. It only happens on certain LINUX machines. We haven't found a pattern yet.
No errors were thrown, it just returns incorrect results on certain LINUX installs.
LINUX version 2.6.31.6-166.fc12.x86_64 (mockbuild@x86-1.fedora.phx.redhat.com) (gcc version 4.4.2 20091027 (Red Hat 4.4.2-7) (GCC) )
How to repeat:
I use this query:
-- Q1:
SELECT count(*)
FROM posCustomer
JOIN acrCustomer
ON posCustomer.customerId = acrCustomer.customerId
AND ( acrCustomer.securityPerms IS NULL OR acrCustomer.securityPerms NOT LIKE '%[01-1=N]%')
JOIN acrCustomer masterCustomer
ON masterCustomer.customerNumber = acrCustomer.customerNumber
AND masterCustomer.entityId = acrCustomer.entityId
AND masterCustomer.customerSubacct = '00000'
WHERE posCustomer.customerStatus = 'A'
AND posCustomer.entityId = '01'
AND posCustomer.customerSubacct = '00000'
AND posCustomer.recordType NOT IN ( 'INTSTORE', 'INHOUSE' )
AND posCustomer.entityId = '01'
AND ( masterCustomer.securityPerms IS NULL OR masterCustomer.securityPerms NOT LIKE '%[01-1=N]%' )
and it returns a few hundred results. (I'm doing a count(*) for this example. The actual query I use specifies columns from each of the tables.)
When I add a nested LEFT JOIN:
-- Q2:
SELECT count(*)
FROM posCustomer
JOIN acrCustomer
ON posCustomer.customerId = acrCustomer.customerId
AND ( acrCustomer.securityPerms IS NULL OR acrCustomer.securityPerms NOT LIKE '%[01-1=N]%')
JOIN acrCustomer masterCustomer
ON masterCustomer.customerNumber = acrCustomer.customerNumber
AND masterCustomer.entityId = acrCustomer.entityId
AND masterCustomer.customerSubacct = '00000'
LEFT JOIN (
acrCustAddress primaryAddress
JOIN acrCustContact primaryContact
ON primaryContact.addressId = primaryAddress.addressId
AND primaryContact.primaryContact = 'Y'
AND primaryContact.startDate <= DATE_FORMAT(CURDATE(),'%Y/%m/%d')
AND ( primaryContact.endDate IS NULL OR primaryContact.endDate >= DATE_FORMAT(CURDATE(),'%Y/%m/%d') )
LEFT JOIN sysState primaryCity
ON primaryCity.stateCityId = primaryAddress.cityId
LEFT JOIN sysState primaryState
ON primaryState.stateCityId = primaryAddress.stateId
LEFT JOIN acrCustContactMethod primaryPhone
ON primaryPhone.addressId = primaryContact.addressId
AND primaryPhone.contactUniqueId = primaryContact.uniqueId
AND primaryPhone.contactMethod = 'P'
AND primaryPhone.defaultMethod = 'Y'
LEFT JOIN acrCustContactMethod primaryFax
ON primaryFax.addressId = primaryContact.addressId
AND primaryFax.contactUniqueId = primaryContact.uniqueId
AND primaryFax.contactMethod = 'F'
AND primaryFax.defaultMethod = 'Y'
LEFT JOIN acrCustContactMethod primaryEmail
ON primaryEmail.addressId = primaryContact.addressId
AND primaryEmail.contactUniqueId = primaryContact.uniqueId
AND primaryEmail.contactMethod = 'L'
AND primaryEmail.defaultMethod = 'Y'
)
ON primaryAddress.customerId = acrCustomer.customerId
AND primaryAddress.startDate <= DATE_FORMAT(CURDATE(),'%Y/%m/%d')
AND ( primaryAddress.endDate IS NULL OR primaryAddress.endDate >= DATE_FORMAT(CURDATE(),'%Y/%m/%d') )
WHERE posCustomer.customerStatus = 'A'
AND posCustomer.entityId = '01'
AND posCustomer.customerSubacct = '00000'
AND posCustomer.recordType NOT IN ( 'INTSTORE', 'INHOUSE' )
AND posCustomer.entityId = '01'
AND ( masterCustomer.securityPerms IS NULL OR masterCustomer.securityPerms NOT LIKE '%[01-1=N]%' )
my results are limited to 7. Those 7 are the 7 records in acrCustContact aliased as primaryContact. There should be a few hundred results since I'm only adding on a LEFT JOIN. But for some reason, the JOIN to acrCustContact aliased primaryContact is limiting my results.
Oddly, I can get my hundreds of results by doing one of the following:
* Change the join to primaryContact to a LEFT JOIN
* Remove any one of the LEFT JOINs in the nested LEFT JOIN (such as sysState which is aliased as primaryCity)
Suggested fix:
This is how I "fixed" it. I turned my nested LEFT JOIN into a subquery and that gave me the correct results that I expected and usually get from Q2. (In this example for Q3, I use "SELECT *", but in reality I of course specified columns)
-- Q3
SELECT COUNT(*)
FROM posCustomer
JOIN acrCustomer
ON posCustomer.customerId = acrCustomer.customerId
AND ( acrCustomer.securityPerms IS NULL OR acrCustomer.securityPerms NOT LIKE '%[01-1=N]%')
JOIN acrCustomer masterCustomer
ON masterCustomer.customerNumber = acrCustomer.customerNumber
AND masterCustomer.entityId = acrCustomer.entityId
AND masterCustomer.customerSubacct = '00000'
LEFT JOIN (
Select *
From acrCustAddress primaryAddress
JOIN acrCustContact primaryContact
ON primaryContact.addressId = primaryAddress.addressId
AND primaryContact.primaryContact = 'Y'
AND primaryContact.startDate <= DATE_FORMAT(CURDATE(),'%Y/%m/%d')
AND ( primaryContact.endDate IS NULL OR primaryContact.endDate >= DATE_FORMAT(CURDATE(),'%Y/%m/%d') )
LEFT JOIN sysState primaryCity
ON primaryCity.stateCityId = primaryAddress.cityId
LEFT JOIN sysState primaryState
ON primaryState.stateCityId = primaryAddress.stateId
LEFT JOIN acrCustContactMethod primaryPhone
ON primaryPhone.addressId = primaryContact.addressId
AND primaryPhone.contactUniqueId = primaryContact.uniqueId
AND primaryPhone.contactMethod = 'P'
AND primaryPhone.defaultMethod = 'Y'
LEFT JOIN acrCustContactMethod primaryFax
ON primaryFax.addressId = primaryContact.addressId
AND primaryFax.contactUniqueId = primaryContact.uniqueId
AND primaryFax.contactMethod = 'F'
AND primaryFax.defaultMethod = 'Y'
LEFT JOIN acrCustContactMethod primaryEmail
ON primaryEmail.addressId = primaryContact.addressId
AND primaryEmail.contactUniqueId = primaryContact.uniqueId
AND primaryEmail.contactMethod = 'L'
AND primaryEmail.defaultMethod = 'Y'
) as query1
ON query1.customerId = acrCustomer.customerId
AND query1.startDate <= DATE_FORMAT(CURDATE(),'%Y/%m/%d')
AND ( query1.endDate IS NULL OR query1.endDate >= DATE_FORMAT(CURDATE(),'%Y/%m/%d') )
WHERE posCustomer.customerStatus = 'A'
AND posCustomer.entityId = '01'
AND posCustomer.customerSubacct = '00000'
AND posCustomer.recordType NOT IN ( 'INTSTORE', 'INHOUSE' )
AND posCustomer.entityId = '01'
AND ( masterCustomer.securityPerms IS NULL OR masterCustomer.securityPerms NOT LIKE '%[01-1=N]%' )
