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.
© 2025, Oracle Corporation and/or its affiliates
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.