MySQL Bugs: #85027: MySqlParameter int zero evaluated to null

Bug #85027	MySqlParameter int zero evaluated to null
Submitted:	16 Feb 2017 17:49	Modified:	17 Jul 2020 14:25
Reporter:	Mike Vyce	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.9.9	OS:	Windows (10 x64)
Assigned to:		CPU Architecture:	Any

Description:
Really simple one, amazed I can't find anything previously about it (so it's probably my mistake somewhere)

Simplified table definition is:
CREATE TABLE `auditsenabled` (
  `ProviderId` int(11) NOT NULL,
  `Permanent` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ProviderId`,`AuditId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The following code:

MySqlParameter[] parameters = new[] {
    new MySqlParameter("IsFalse", 0)
};
string query = "SELECT * FROM table1 t1 WHERE t1.Permanent = ?IsFalse";
MySqlHelper.ExecuteDataset(ConnectionString, query, parameters);

Does not return any rows.

However, this does return rows where Permanent = 0:

MySqlParameter[] parameters = new[] {
    new MySqlParameter("IsFalse", 0)
};
string query = "SELECT * FROM table1 t1 WHERE t1.Permanent = 0";
MySqlHelper.ExecuteDataset(ConnectionString, query, parameters);

And this works too:
MySqlParameter[] parameters = new[] {
    new MySqlParameter("IsFalse", "0")   // Note the zero is now a string
};

Even: 
MySqlParameter[] parameters = new[] {
    new MySqlParameter("IsFalse", false)   // Now boolean..
};
...returns rows where Permanent = 0.

But any numeric declaration of zero: 0, 0d, 0m, 0f
Fails to return any rows.

If I alter the query to be deliberately wrong, like so:
MySqlParameter[] parameters = new[] {
    new MySqlParameter("IsFalse", 0)
};
string query = "SELECT * FROM table1 t1 WHERE t1.Permanent == ?IsFalse";  // Doubled equals-signs
MySqlHelper.ExecuteDataset(ConnectionString, query, parameters);

Then in the output error, I can see that ?IsFalse has been evaluated to NULL.  Which I _believe_ to be incorrect behaviour.

How to repeat:
CREATE TABLE `auditsenabled` (
  `ProviderId` int(11) NOT NULL,
  `Permanent` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ProviderId`,`AuditId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
MySqlParameter[] parameters = new[] {
    new MySqlParameter("IsFalse", 0)
};
string query = "SELECT * FROM table1 t1 WHERE t1.Permanent = ?IsFalse";
MySqlHelper.ExecuteDataset(ConnectionString, query, parameters);

Fails to return rows where Permanent equals zero.

Hello  Mike Vyce,

Thank you for the bug report and test case.
Verified this behavior on Visual Studio 2013 (C#.Net) and Connector/NET 6.9.9 version.

Thanks,
Chiranjeevi.

Screenshot

Attachment: 85027.JPG (image/jpeg, text), 194.06 KiB.

Screenshot

Attachment: 85027_1.JPG (image/jpeg, text), 199.64 KiB.

Thanks Chiranjeevi.

A little more info - 

Creating the parameter, and then setting the type exhibits the same behaviour:

MySqlParameter[] parameters = new[] {
    new MySqlParameter("IsFalse", 0)   
};
parameters[0].MySqlDbType = MySqlDbType.Int32;

BUT if the value is assigned *after* the type has been defined, like so:

MySqlParameter[] parameters = new[] {
    new MySqlParameter("IsFalse", 0)   
};
parameters[0].MySqlDbType = MySqlDbType.Int32;
parameters[0].Value = 0;

...then it DOES work.

Sorry - the above comment is inaccurate, please disregard.

What I have actually found is that simply re-setting the paramter's Value afterwards, like so:

MySqlParameter[] parameters = new[] {
    new MySqlParameter("IsFalse", 0)   
};
parameters[0].Value = 0;

...DOES work.

Ok, so the reason for this behaviour is because of zero being "implicitly convertible" to enum as per: 
http://stackoverflow.com/questions/2043554/method-overload-resolution-unexpected-behavior

And it's therefore using the unintended overload:
MySqlParameter(string parameterName, MySqlDbType dbType)

Which in turn calls the constructor I was aiming for, but with 'value' set to null.

Defining a new constructor:
public MySqlParameter(string parameterName, int numValue) : this(parameterName, value: numValue) {}

...with a closer matching type works for my specific case, but once you do one, then really you'd have to define these 'dumb' constructors for each numeric type.
Hopefully someone smarter than me can think of a neater way around.

In the meantime, I'll switch to specifying the constructor I mean to use (which also works), like so:
new MySqlParameter("IsFalse", value: 0)

Dropped the severity as you could argue it isn't actually a bug (or a feature :P) in the connector.  It's just what C# does.

On the other hand, handling zero values would make it behave much more intuitively.

Posted by developer:
 
added the logic to initialize the numeric type inferred with zero

Posted by developer:
 
added the logic to initialize the numeric type inferred with zero

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.22 release, and here's the proposed changelog entry from the documentation team:

The default type mapping of the MySqlParameter constructor with the
parameter value of zero was to the MySqlDbType enumeration, which
evaluated to NULL. This fix maps the default type to int32.

Thank you for the bug report.

Thanks for fixing it.  Is it possible to see the source change already, or only after it's released?