MySQL Bugs: #5597: MySQL Connector/Net has a problem with the "LEFT JOIN" syntax

Bug #5597	MySQL Connector/Net has a problem with the "LEFT JOIN" syntax
Submitted:	15 Sep 2004 16:34	Modified:	16 Sep 2004 12:47
Reporter:	Nate Jones	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	1.0.0 Beta	OS:	Windows (Windows XP SP2)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
We're having a problem with the MySQL Connector/Net data provider for .Net.  We have a function that returns a datatable (we've tried it with a datareader also), that uses the following sql syntac:

Private Sub GetLeadSourceData()
        Dim db As clsDatabase = New clsDatabase()
        Dim SQL As String

        SQL = "SELECT l.LeadSourceID, l.CompanyName, l.CompanyURL, l.Address, l.City, l.State, l.ZipCode, l.SignUpDate, l.Status, l.UTCOffset, l.DupeByLeadSource, l.DupeDaysBack, l.CompanyID, a.AFID, a.Description, a.PaymentType, a.PricePerLead, a.PixelHTML, a.Inactive, a.JumpPageURL, a.ProjectID "
        SQL &= "FROM LeadSources l  Left JOIN AFIDLIST a ON l.LeadSourceID = a.LeadSourceID "
        SQL &= "WHERE l.CompanyID = " & CurrentCompany.CompanyID & " "
        SQL &= "ORDER BY l.Status DESC, l.CompanyName ASC, l.LeadSourceID ASC, a.AFID ASC "

        DisplayDT = db.FillDataTable(SQL, "")

    End Sub

The data returned will only be the data from the left side of the join.  All the data from the AFIDLIST a side will return dbnull values "[NULL]".  

How to repeat:
     To repeat the problem, setup a query that uses a Left JOIN and try running the query using the MySQL Connector/Net data provider. (****IMPORTANT:  If you use the ODBC.NET data provider from Microsoft and the MyODBC Driver, the exact same function mentioned above works perfectly!  We just recently switched to the Connecter/Net provider and noticed the problem immediately *****)  We're using the .Net Framework Version 1.0 (Visual Studio 2002), programming in VB.NET.  You will notice that the data returned (Doesn't matter if you use a dataset, datatable, or a datareader...the results returned are the same), will be all NULL values for all the fields on the right side of the join.  (In our case this is everything from the AFIDLIST a  side of the JOIN).

Please let me know if there is a way to fix this quickly.  We're using the JOIN syntax so that we don't have to run 2 or 3 different queries to get the same results.

Suggested fix:
Don't know.  Hopefully it's an easy fix.  Please let me know what I can do to help.

5571 and 5565 are also issues with nulls.  I included a patch in 5571; you might want to see if that helps with your issue.  (No warranties or promises on the patch; I'm not an official mysql developer by any means)

James,

You are absolutely correct in saying that this is a null field issue.  I dove a little deeper into the problem, and the exact problem is that once a field says it has a null value, for every row after that, the column is still set to null.  The fix you did in Bug #5571 is the exact same fix that I did for mine, and it fixed it.  Thanks for sharing and commenting on bugs!  Your help is greatly appreciated.

This is a duplicate of bug #5388 and has already been fixed.