Expected way to convert MySQL floating point calculations?

[JustArchi]

I'm executing SQL query which includes inside:

SELECT SUM(value) / (SELECT points_awarded FROM users WHERE ...) FROM winners WHERE ...

Since I'm not interested in absolute precision (I round it anyway), in original MySQL connector I used:

var result = dataReader.GetFloat(0);

And that worked just fine, but after switching to your library I started getting in that place:

System.InvalidCastException: Unable to cast object of type 'System.Decimal' to type 'System.Single'.

Now, I understand that I can always do (float) dataReader.GetDecimal(0), which is in fact what I did, but I'm wondering if there is no better way to approach this issue. I'd expect that such conversion could be done in the library itself, but then again, maybe there is some way to declare precision of calculation or in other way point right away that we're not interested in using decimals? I didn't even know that under the hood there is a decimal used in the first place, so as an user it was weird to me that the library didn't do the conversion automatically, especially considering that it's possible with just a cast.

Or maybe instead of all of that I should just use CAST(... AS FLOAT) in the SQL query itself?

Thank you in advance for answer, as well as awesome work that you put into library in the first place, I love it! ❤️

[bgrainger]

Now, I understand that I can always do (float) dataReader.GetDecimal(0), which is in fact what I did, but I'm wondering if there is no better way to approach this issue. I'd expect that such conversion could be done in the library itself

MySqlConnector currently performs very few implicit conversions. This may be a bug; I'll need to extend AdoNetApiTest to examine what other providers generally do here.

Note that SqlClient explicitly says:

No conversions are performed. Therefore, the data retrieved must already be a single-precision floating point number.

But obviously there may be good reasons to be less strict for backwards compatibility with Connector/NET.

I recommend sticking with the explicit (float) cast for now while I investigate.

[JustArchi]

Just to note - 100% compatibility with Connector/NET isn't good either. Just as an example - I actually found at least two cases in my code where I used GetByte() but in fact had signed byte in the database. Of course, those were just small mistakes since in both cases I had only numbers from 0 up to 10 max, but then again, Connector/NET was totally fine with it as long as retrieved/inserted value matched given format, regardless of what in fact database was configured to use. I was super happy when I noticed that MySqlConnector explicitly threw exception on me that it can't convert signed byte to byte - this allowed me to correct type in the database to be unsigned, and in fact this is what should happen, because you can't guarantee sbyte -> byte conversion to succeed in every scenario.

If you ask me, then I think that MySqlConnector could perform fixed set of "safe" conversions, such as byte -> ushort or decimal -> float, mainly because there is no possibility of a cast to fail - at worst, we'll lose some precision or allocate more memory than needed for given value to fit.

Then again, this should probably be an option to connection string, I'm very grateful for MySqlConnector being explicit in this regard and I'd rather use those explicit settings everywhere, even if meant casts in SQL queries, so thank you a lot for that.

Since there is already similar issue in your repo for this case, I'll close this one and just reference it instead - mysql-net/AdoNetApiTest#4

Thank you a lot for answer!