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:Microsoft Windows (10 x64)
Assigned to: CPU Architecture:Any

[16 Feb 2017 17:49] Mike Vyce
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.
[17 Feb 2017 8:42] Chiranjeevi Battula
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.
[17 Feb 2017 8:55] Chiranjeevi Battula
Screenshot

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

[17 Feb 2017 8:55] Chiranjeevi Battula
Screenshot

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

[20 Feb 2017 9:27] Mike Vyce
Thanks Chiranjeevi.
[21 Feb 2017 14:32] Mike Vyce
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.
[21 Feb 2017 14:39] Mike Vyce
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.
[21 Feb 2017 16:59] Mike Vyce
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)
[22 Feb 2017 14:43] Mike Vyce
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.
[10 Jul 2020 21:06] Gustavo Cuatepotzo
Posted by developer:
 
added the logic to initialize the numeric type inferred with zero
[10 Jul 2020 21:06] Gustavo Cuatepotzo
Posted by developer:
 
added the logic to initialize the numeric type inferred with zero
[16 Jul 2020 15:23] Christine Cole
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.
[17 Jul 2020 14:25] Mike Vyce
Thanks for fixing it.  Is it possible to see the source change already, or only after it's released?