Bug #45206 Querying with Guid as parameter in where statement yeilds no results

• Submitted: 30 May 2009 13:50
• Modified: 2 Jun 2009 19:24
• Reporter: Muhammad Mosa
• Status: Can't repeat
• Category: Connector / NET
• Severity: S2 (Serious)
• Version: 6.03
• OS: Any
• CPU Architecture: Any
• Tags: Binary(16), Guid, UUID

[30 May 2009 13:50] Muhammad Mosa

Description:
Trying to query a database table that has Guid filed -binary(16)- as primary using a statement similar to the below:
var cmd = new MySqlCommand("select * from testtable where Id=@id", conn);
var param = new MySqlParameter("id", MySqlDbType.Guid);
param.Value = "8d8938e0-4d04-11de-9869-485cf6bccbc5";
cmd.Parameters.Add(param);
cmd.CommandType = CommandType.Text;
conn.Open();
var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while(reader.Read())
{
  Console.WriteLine(reader.GetGuid("Id"));
}

How to repeat:
1)Create a table with binary(16) field.
2)Insert a record in the field.
3)Try to query the database using the below code:
var cmd = new MySqlCommand("select * from testtable where Id=@id", conn);
var param = new MySqlParameter("id", MySqlDbType.Guid);
param.Value = new Guid("YOUR-GUID-STRING");
cmd.Parameters.Add(param);
cmd.CommandType = CommandType.Text;
conn.Open();
var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while(reader.Read())
{
  Console.WriteLine(reader.GetGuid("Id"));
}

[30 May 2009 19:16] Muhammad Mosa

I guess this bug is related to MySqlGuid.WriteValue method.
when I modified the last portion of this method as the following I was able to fetch my records using Guid where my field of course of type binary(16):
if (binary)
{
   byte[] bytes = guid.ToByteArray();
   packet.WriteLength(bytes.Length);
   packet.Write(bytes);
}
else
{
   //Core modified lines
   var guidAsBinaryString = String.Format("0x{0}", guid.ToString("N"));
   packet.WriteStringNoNull(guidAsBinaryString);
}

[2 Jun 2009 19:24] Reggie Burnett

Unable to reproduce this. How did you insert the guid values?  Here is the test case I used to attempt to reproduce.

        /// <summary>
        /// Bug #45206	Querying with Guid as parameter in where statement yeilds no results
        /// </summary>
        [Test]
        public void GuidOnWhereClause()
        {
            execSQL("CREATE TABLE Test(id INT, g BINARY(16))");

            Guid guid = Guid.NewGuid();
            MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES(1, @g)", conn);
            cmd.Parameters.Add(new MySqlParameter("@g", MySqlDbType.Guid));
            cmd.Parameters[0].Value = guid;
            cmd.ExecuteNonQuery();

            cmd.CommandText = "SELECT * FROM Test WHERE g=@g";
            cmd.Parameters[0].Value = guid.ToString("D");
            using (MySqlDataReader r = cmd.ExecuteReader())
            {
                r.Read();
                Guid g = r.GetGuid("g");
                Assert.AreEqual(guid, g);
            }
        }