Bug #85027 MySqlParameter int zero evaluated to null
Submitted: 16 Feb 2017 17:49 Modified: 22 Feb 2017 14:43
Reporter: Mike Vyce Email Updates:
Status: Verified 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.