Bug #17454 WRONG RESULT WHEN JOINING TABLES...
Submitted: 16 Feb 2006 13:37 Modified: 17 Mar 2006 12:52
Reporter: worgie flores Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.16-nt OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[16 Feb 2006 13:37] worgie flores
Description:
I have encountered a very serious bug upon joining tables in the select statement. And you wont be believe it... It doesn't give you an error message but it gives you a disgusting result or whatever...
What I do is:
   Let's say I have TABLE1 as my base table which has 1000 records thats joins (ONE-ON-ONE) the TABLE2, and TABLE2 joins (ONE-ON-ONE) the TABLE3. And my filter is TABLE1.objid=<myfiltervalue>. 
  And the result returns the whole records of TABLE1. 

Anyways, I still got a solution. But that's not the correct behaviour of that statement. When you run this statement to older versions it gives a correct result...

You need to have an early quick fix on this or it gets worst.........

How to repeat:
=============================================================
This statement gives me a wrong result, it returns the whole records of the jobseeker table.
=============================================================
SELECT 
     * 
FROM 
     jobseeker j
          INNER JOIN person p ON p.objid = j.objid
          INNER JOIN personinfo pf ON pf.objid = p.objid
WHERE
     j.objid='JS-123456789'
      

Suggested fix:
=============================================================
This statement gives me what I really want...
=============================================================
SELECT 
     * 
FROM 
     jobseeker j
          INNER JOIN person p ON p.objid = j.objid
          INNER JOIN personinfo pf ON pf.objid = j.objid
WHERE
     j.objid='JS-123456789'

OR

SELECT 
     * 
FROM 
     jobseeker j
          INNER JOIN person p ON p.objid = j.objid
          INNER JOIN personinfo pf ON pf.objid = pf.objid
WHERE
     p.objid='JS-123456789'
[17 Feb 2006 12:52] Valeriy Kravchuk
Thank you for a problem report. Please, send the results of SHOW CREATE TABLE statement for all the tables mentioned in your SELECT: jobseeker, person and personinfo. Some test data for these tables, that demonstrates the problem, will be also useful.

Please, also try to use newer version of MySQL server, 5.0.18.
[18 Mar 2006 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".