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

[10 Aug 2019 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 2019 11:45] MySQL Verification Team 
Hello Bradley,

Thank you for the report and test case.

regards,
Umesh
[12 Mar 2020 18:40] Christine Cole 
Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.21 release, and here's the proposed changelog entry from the documentation team:

The BLOB type was inferred internally when a value or object of type
MySqlGeometry was used in different situations, which caused to server to
return either zero matching rows or an exception.

Thank you for the bug report.