Bug #1703 SELECT DISTINCT not showing a distinct row
Submitted: 29 Oct 2003 10:12 Modified: 30 Oct 2004 9:04
Reporter: Tony Little Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:4.0.12 OS:Linux (Slackware linux)
Assigned to: Assigned Account CPU Architecture:Any

[29 Oct 2003 10:12] Tony Little
Description:
When running the following queries:
DROP TABLE IF EXISTS rmallaccountinfo;

---BEGIN QUERIES---

CREATE TEMPORARY TABLE rmallaccountinfo SELECT c.accno, c.pid, u.unit, u.unittype, c.unitid, c.status, c.depositdate,   c.astreet1, c.astreet2, c.acity, c.astate, c.azip, c.pstreet1, c.pstreet2,   c.pcity, c.pstate, c.pzip, c.movein, c.moveout, c.leasest, c.leasend, c.deposit,   c.display, c.active, c.weekly, c.archive, c.rdue, c.stoppay, c.stoplate,   c.stopstatement, c.comment AS custcomment, n.fname, n.lname, n.bdate, n.ssn,   n.hphone, n.pphone, n.wphone, n.fax, n.carphone, n.altphone, n.email, n.pager,   n.comments AS namecomments, n.license, n.car, n.pictfile, n.isprimary,   n.showonbill, n.employer, n.iscompany, u.street1 AS unitstreet1,   u.street2 AS unitstreet2, u.city AS unitcity, u.state AS unitstate,   u.zip AS unitzip, u.comments AS unitcomments, u.sqft, u.meterid, t.typeid,   t.descrip AS typedescrip, p.name, p.shortname, p.rctype FROM (((cust AS c LEFT JOIN custnames AS n ON c.accno=n.accno)   LEFT JOIN unit AS u ON c.unitid=u.uniquerecid)   LEFT JOIN unittype AS t ON u.unittype=t.id)   LEFT JOIN prop AS p ON c.pid=p.pid ORDER BY n.lname, n.fname;

ALTER TABLE rmallaccountinfo ADD INDEX rminfo_acc (accno);

SELECT DISTINCT i.itemid, i.descrip 
FROM rmAllAccountInfo c, auto a, items i 
WHERE c.isprimary<>0    AND c.pid IN (5)    AND c.movein<='2003-10-29'    AND (c.moveout>='2003-10-29' OR c.moveout IS NULL)    AND (c.accno=a.accno OR c.pid=a.pid OR c.unitid=a.unitid OR c.unittype=a.unittype)    AND a.itemid=i.itemid 
ORDER BY i.itemid;

----End queries----

---Result---
+--------+-------------------------------+
| itemid | descrip                       |
+--------+-------------------------------+
| AN     | Annual RV Rental              |
| EU     | Employee Unit                 |
| PM     | Park Model                    |
| RP     | Rent Promotion/Concession     |
| RV     | Short Term Rv Rental          |
| ST     | Storage                       |
| T1     | RV Residential Tax            |
| T2     | Storage Commercial Tax Charge |
| T3     | SW Residential Tax            |
| T4     | DW Residential Tax            |
| T5     | Short Term RV Tax             |
+--------+-------------------------------+
---END RESULT---

The last SELECT DISTINCT query loses one of the distinct combinations (RC,Rent Charge) of i.itemid, i.descrip.  However if GROUP BY is used instead as in

SELECT i.itemid, i.descrip 
FROM rmAllAccountInfo c, auto a, items i 
WHERE c.isprimary<>0    AND c.pid IN (5)    AND c.movein<='2003-10-29'    AND (c.moveout>='2003-10-29' OR c.moveout IS NULL)    AND (c.accno=a.accno OR c.pid=a.pid OR c.unitid=a.unitid OR c.unittype=a.unittype)    AND a.itemid=i.itemid 
GROUP BY i.itemid, i.descrip 
ORDER BY i.itemid;

The result is correct:
+--------+-------------------------------+
| itemid | descrip                       |
+--------+-------------------------------+
| AN     |              Annual RV Rental |
| EU     |                 Employee Unit |
| PM     |                    Park Model |
| RC     |                   Rent Charge |  <--- this is the one missing with
| RP     |     Rent Promotion/Concession |       the SELECT DISTINCT
| RV     |          Short Term Rv Rental |
| ST     |                       Storage |
| T1     |            RV Residential Tax |
| T2     | Storage Commercial Tax Charge |
| T3     |            SW Residential Tax |
| T4     |            DW Residential Tax |
| T5     |             Short Term RV Tax |
+--------+-------------------------------+

---
If the DISTINCT keyword is removed then the RC,Rent Charge combination shows up in the list.

How to repeat:
Stated in the description of the problem.
[29 Oct 2003 12:56] Dean Ellis
Some bugs involving DISTINCT and GROUP BY were corrected in later versions.

Please upgrade to the current stable release (4.0.16) and see if the issue persists.

Thank you