MySQL Bugs: #118978: missing rows in query result

Bug #118978	missing rows in query result
Submitted:	9 Sep 2025 16:11	Modified:	22 Apr 12:44
Reporter:	Johannes Wingold	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	9.4.0	OS:	Windows (10)
Assigned to:		CPU Architecture:	Any
Tags:	MySql.Data, VB.NET

Description:
When using the VB.NET connector to run a query containing a LEFT JOIN, WHERE and ORDER BY, only one (1) row is returned even when mutliple rows match the condition

MySql.Data version 9.4.0
VB .NET Framework 4.8.09037 in WPF application Project.
MySQL Server version 5.7.42 (MySQL Community Server (GPL))
Windows 10

How to repeat:
#run the following commands on your DB server:
CREATE TABLE `tblA` (
   `pkA` int(11) NOT NULL,
   PRIMARY KEY (`pkA`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

INSERT INTO `tblA` (`pkA`) VALUES (1);
INSERT INTO `tblA` (`pkA`) VALUES (2);
INSERT INTO `tblA` (`pkA`) VALUES (3);
INSERT INTO `tblA` (`pkA`) VALUES (4);
INSERT INTO `tblA` (`pkA`) VALUES (5);

# run the following code (VB .NET 4.8), requires MySQL.Data and all its dependencies

Public Sub CountRows(strCMD As String)
   Dim dtblRead As New System.Data.DataTable()
   Dim conMySQL As New MySql.Data.MySqlClient.MySqlConnection
   conMySQL.ConnectionString = String.Format("server={0};uid={1};pwd={2};database={3}; ", "255.255.255.255", "user", "password", "db")
   conMySQL.Open()
   Using sqlCMD As MySql.Data.MySqlClient.MySqlCommand = conMySQL.CreateCommand()
      sqlCMD.CommandText = strCMD
      Using reader As MySql.Data.MySqlClient.MySqlDataReader = sqlCMD.ExecuteReader()
         dtblRead.Load(reader)
      End Using
   End Using
   println("{dtblRead.Rows.Count()} row") # replace println with the output function of your choice.
End Sub

CountRows("SELECT * FROM tblA As a LEFT JOIN tblA As b ON a.pkA < b.pkA WHERE a.pkA = 1;") ' prints 4 row
CountRows("SELECT * FROM tblA As a LEFT JOIN tblA As b ON a.pkA < b.pkA WHERE a.pkA = 1 ORDER BY b.pkA;") ' prints 1 row

I expect the second query to return the same rows as the first query, only in different order, but the second query only contains the last row of the first query.
Executing the same Queries in MySQL workbench leads to the correct results, so its likely a bug in the MySQL.data library for .NET

Suggested fix:
make that the query "SELECT * FROM tblA As a LEFT JOIN tblA As b ON a.pkA < b.pkA WHERE a.pkA = 1 ORDER BY b.pkA;" returns the correct number of rows when used with MySQL.Data for VB .NET.

Thanks for report

Posted by developer:
 
Retrieving data via a data reader no longer fetches key metadata by default. This prevents incorrect key inference, which was the cause of the reported behavior

Posted by developer:
 
Added the following note to the MySQL Connector/NET 9.7.0 release notes:	

Retrieving data with a data reader did not fetch key metadata by default, 
preventing incorrect key inference. As a result, queries containing a 
LEFT JOIN, WHERE, and ORDER BY returned a single row, even though multiple rows matched the condition

Todays 9.7 update of MySQL.Data solved the Issue for me, thanks to whom it may concern.