Description:
A query using LEFT JOIN returns the wrong result. I changed from ByteFX 0.76 to the new MySQL Connector/Net, without touching the database backend, and found this problem. I am running mysqld 4.1.3-beta.
The LEFT JOIN in the example below should return a row (3, 5, '35text') but returns (3, 5, NULL) instead.
How to repeat:
using System; // String
using System.Data; // IDbConnection
using MySql.Data.MySqlClient;
using NUnit.Framework;
namespace foo
{
/// <summary>
/// Test for new mysql connector
/// </summary>
[TestFixture]
public class MysqlConnectorTest
{
// You will need to customize this string
public static string connectionString = "Database=test;Data Source=db.hostname;User ID=youruser;Password=yourpass";
public MysqlConnectorTest()
{
}
private int ExecuteNonQuery(MySqlConnection con, string query)
{
MySqlCommand cmd = new MySqlCommand(query, con);
int rowCnt = cmd.ExecuteNonQuery();
return rowCnt;
}
/// <summary>
/// Test a left join
/// </summary>
[Test]
public void LeftJoinTest()
{
MySqlConnection con = new MySqlConnection(connectionString);
con.Open();
try
{
this.ExecuteNonQuery(con, "CREATE TABLE foo (foo1 bigint, foo2 bigint, PRIMARY KEY (foo1, foo2))");
this.ExecuteNonQuery(con, "CREATE TABLE bar (bar1 bigint, bar2 bigint, barval TEXT, PRIMARY KEY (bar1, bar2))");
this.ExecuteNonQuery(con, "INSERT INTO foo VALUES (1, 2)");
this.ExecuteNonQuery(con, "INSERT INTO foo VALUES (2, 3)");
this.ExecuteNonQuery(con, "INSERT INTO foo VALUES (3, 4)");
this.ExecuteNonQuery(con, "INSERT INTO foo VALUES (3, 5)");
this.ExecuteNonQuery(con, "INSERT INTO bar VALUES (3, 5, '35text')");
string JOIN_QRY = "SELECT i1.foo1, i1.foo2, barval FROM foo as i1 LEFT JOIN bar ON i1.foo1 = bar1 AND i1.foo2 = bar2 WHERE i1.foo1={0}";
string qry = String.Format(JOIN_QRY, "3");
MySqlCommand cmd = new MySqlCommand(qry, con);
IDataReader rdr = cmd.ExecuteReader();
bool failed = false;
while (rdr.Read())
{
long foo1 = rdr.GetInt64(0);
long foo2 = rdr.GetInt64(1);
string fooval = rdr.IsDBNull(2) ? "" : rdr.GetString(2);
System.Console.WriteLine(foo1 + ", " + foo2 + ", " + fooval);
if (foo1 == 3 && foo2 == 5)
{
if (fooval == "")
{
System.Console.WriteLine("Error");
failed = true;
}
else
{
System.Console.WriteLine("ok");
}
}
}
rdr.Close();
Assert.IsFalse(failed, "LEFT JOIN returned incorrect result");
}
finally
{
this.ExecuteNonQuery(con, "DROP TABLE IF EXISTS foo");
this.ExecuteNonQuery(con, "DROP TABLE IF EXISTS bar");
con.Close();
}
}
}
}
Suggested fix:
The strangest thing about this bug is, if I change "using MySql.Data.MySqlClient" to "using ByteFX.Data.MySqlClient" (I have both ByteFX 0.76 and MySql Connector 1.0.0 Beta installed), then the test passes. Also, when I run the queries on the linux command-line mysql client, I get the correct result. So it must be a bug in the new Connector client library.
Description: A query using LEFT JOIN returns the wrong result. I changed from ByteFX 0.76 to the new MySQL Connector/Net, without touching the database backend, and found this problem. I am running mysqld 4.1.3-beta. The LEFT JOIN in the example below should return a row (3, 5, '35text') but returns (3, 5, NULL) instead. How to repeat: using System; // String using System.Data; // IDbConnection using MySql.Data.MySqlClient; using NUnit.Framework; namespace foo { /// <summary> /// Test for new mysql connector /// </summary> [TestFixture] public class MysqlConnectorTest { // You will need to customize this string public static string connectionString = "Database=test;Data Source=db.hostname;User ID=youruser;Password=yourpass"; public MysqlConnectorTest() { } private int ExecuteNonQuery(MySqlConnection con, string query) { MySqlCommand cmd = new MySqlCommand(query, con); int rowCnt = cmd.ExecuteNonQuery(); return rowCnt; } /// <summary> /// Test a left join /// </summary> [Test] public void LeftJoinTest() { MySqlConnection con = new MySqlConnection(connectionString); con.Open(); try { this.ExecuteNonQuery(con, "CREATE TABLE foo (foo1 bigint, foo2 bigint, PRIMARY KEY (foo1, foo2))"); this.ExecuteNonQuery(con, "CREATE TABLE bar (bar1 bigint, bar2 bigint, barval TEXT, PRIMARY KEY (bar1, bar2))"); this.ExecuteNonQuery(con, "INSERT INTO foo VALUES (1, 2)"); this.ExecuteNonQuery(con, "INSERT INTO foo VALUES (2, 3)"); this.ExecuteNonQuery(con, "INSERT INTO foo VALUES (3, 4)"); this.ExecuteNonQuery(con, "INSERT INTO foo VALUES (3, 5)"); this.ExecuteNonQuery(con, "INSERT INTO bar VALUES (3, 5, '35text')"); string JOIN_QRY = "SELECT i1.foo1, i1.foo2, barval FROM foo as i1 LEFT JOIN bar ON i1.foo1 = bar1 AND i1.foo2 = bar2 WHERE i1.foo1={0}"; string qry = String.Format(JOIN_QRY, "3"); MySqlCommand cmd = new MySqlCommand(qry, con); IDataReader rdr = cmd.ExecuteReader(); bool failed = false; while (rdr.Read()) { long foo1 = rdr.GetInt64(0); long foo2 = rdr.GetInt64(1); string fooval = rdr.IsDBNull(2) ? "" : rdr.GetString(2); System.Console.WriteLine(foo1 + ", " + foo2 + ", " + fooval); if (foo1 == 3 && foo2 == 5) { if (fooval == "") { System.Console.WriteLine("Error"); failed = true; } else { System.Console.WriteLine("ok"); } } } rdr.Close(); Assert.IsFalse(failed, "LEFT JOIN returned incorrect result"); } finally { this.ExecuteNonQuery(con, "DROP TABLE IF EXISTS foo"); this.ExecuteNonQuery(con, "DROP TABLE IF EXISTS bar"); con.Close(); } } } } Suggested fix: The strangest thing about this bug is, if I change "using MySql.Data.MySqlClient" to "using ByteFX.Data.MySqlClient" (I have both ByteFX 0.76 and MySql Connector 1.0.0 Beta installed), then the test passes. Also, when I run the queries on the linux command-line mysql client, I get the correct result. So it must be a bug in the new Connector client library.