I have just noticed a strange behaviour in my ASP.NET application running both on MySQL and SQL Server.
The application issues plain SQL queries over an EF connection to build a dataset. The result set does not contain any primary surrogate key (the primary key is not part of the SELECT statement). Queries are very complex and I'm not allowed to show them, but I can explain very clearly what happens.
Given the query as
select a.A01, a.A01a, a.A01b, a.A02, a.A03, A11, A12, A12a, A12b, A12c, A21, A22, A23, A24, A25, A31, A31a, A31b, A32, A33, A33a, A33b, A33c, A34, A41, A42, A43, A51, A52, A53, A54, A54a, A54b, A54c, B11, B12, B13, B14, B15, Z0, a.prog, a.utente, C11, D11, D13, D14, D14a, D14b, D14c, D15, D16, D17, D18, D19, D21, D22, D23, D31, D32, D41, D42, D43, D44, D45, Z1
FROM TABLE_A JOIN TABLE_B
WHERE SOME_CONDITION
ORDER BY a.a21 ASC, a.a03 ASC LIMIT 20000
The following code fragment issues the query:
protected DataTable GetDataTableV1(string aSqlStatement)
{
DataTable dtResult = new DataTable();
using (DbContext dataContext = GetDataContext())
{
using (IDbConnection connection = dataContext.Database.Connection)
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = aSqlStatement;
using (IDataReader reader = command.ExecuteReader())
{
dtResult.Load(reader);
}
}
}
}
return dtResult;
}
The query, run both in Workbench and SMSS, returns 1192 results in my case. That is fine. But when the app runs on MYSQL I get only 1156 results!
If I debug into dtResult.Load(reader); running on MySQL I get an unexpected behaviour (and only on MySQL): reader is made of 1192 results.
Obviously, reading the DataReader from debug consumes it, so I can debug again into it. Now it shows 1156 records!
I have examined the result dataset thanks to an "Export to CSV" feature in the code. Remember that the query is ordered by fields A21 and A03 ascending? Looks like that while these fields are not part of primary key, unique, distinct or whatever, DataTable drops rows with matching pairs of them.
I mean that, analysing the resulting dataset, each and every row that is present in MS SQL dataset but is missing in the MySQL dataset shows the same exact pair of columns 1 and 3 of the screenshot below as the next. These columns are respectively C and N in my example. And, again, these columns are not part of the primary key
My question is
Why does DataTable behave like this only on MySQL? How can I include the full result set in my DataTable?
