Bug #96498 WHERE clause using MySqlGeometry as parameter finds no rows
Submitted: 10 Aug 13:28 Modified: 11 Aug 11:45
Reporter: Bradley Grainger (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:8.0.17 OS:Microsoft Windows (10)
Assigned to: CPU Architecture:Other (x64)

[10 Aug 13:28] Bradley Grainger
Description:
Setting MySqlParameter.Value to a MySqlGeometry object, then using the parameter in the WHERE clause of a MySqlCommand matches no rows. The expected behaviour is that the row with the matching GEOMETRY value would be found.

Setting "MySqlParameter.Value = geometry" fails; setting "MySqlParameter.Value = geometry.Value" succeeds. The expected behaviour is that both ways of specifying the parameter value would function identically.

How to repeat:
Create a table with the following data:

DROP TABLE IF EXISTS geometries;
CREATE TABLE geometries(id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, data GEOMETRY);
INSERT INTO geometries(data) VALUES (POINT(1, 1)), (POINT(1, 2));

Run the following C# code:

using (var command = connection.CreateCommand())
{
	command.CommandText = "SELECT id FROM geometries WHERE data = @data;";
	var geometry = new MySqlGeometry(1, 1);

	// NOTE: workaround is to specify 'geometry.Value', not just 'geometry'
	command.Parameters.AddWithValue("@data", geometry);

	// expect "1", get null
	var result = command.ExecuteScalar();
	Console.WriteLine(result); 
}
[11 Aug 11:45] Umesh Shastry
Hello Bradley,

Thank you for the report and test case.

regards,
Umesh