MySQL type conversion: why is float the lowest common denominator type?
I recently ran into an issue where a query was causing a full table scan and it got to the point where the column had a different definition, which I thought was VARCHAR and not INT. When asked with "string_column = 17" the query was running, it just couldn't use the index. It really threw me over the loop.
So I went looking and found what happened, the behavior I saw is consistent with what the MySQL documentation says:
In all other cases, the arguments are compared as floating point (real) numbers.
So my question is, why swim?
I could see an attempt at converting numbers to strings (although the dots on the MySQL page above are good reasons). I could also figure out that I was picking some kind of error or creating an alert (my preference). Instead, he happily works.
So why convert everything to a float? Is this from the SQL standard or some other reason? Can anyone shed some light on this choice for me?
a source to share
I can feel your pain. We have a column in our database that contains what is well known in the company as an "order number". But this is not always a number, in certain circumstances it may have other symbols, so we store it in the varchara. As of SQL Server 2000, this means that "order_number = 123456" is a bad choice. SQL Server effectively rewrites the predicate as "CAST(order_number, INT) = 123456"
having two undesirable effects:
- the index is on
order_number
as varchar, so it runs a full scan - those non-numeric order numbers end up giving a conversion error for the user with a rather useless message.
In a way, it's good that we have these non-numeric "numbers", since at least poorly written queries that pass a parameter as a number fall into a trap and not just suck up resources.
I don't think there is a standard. I seem to remember that PostgreSQL 8.3 removed some of the default assignments between numbers and text types so that this would cause an error when scheduling a query.
Presumably "float" is considered to be the widest numeric type and therefore the one that all numbers can be promoted to?
Oh and similar problems (but no conversion errors) when you have varchar columns and a Java application that passes all string literals as nvarchar ... all of a sudden your varchar indexes are no longer used, good luck finding this happening. You can of course say that the Java application is sending strings as varchar, but now we are stuck with only using characters in windows-1252 because this is what the DB created 5-6 years ago when it was just a "stop solution ", ah-ha.
a source to share
Well, this is easy to understand: float
capable of holding the largest range of numbers.
If the underlying data type is datetime
, for example, it can simply be converted to a floating point number that has the same internal value.
If the datatype is string
, it is easy to parse it with float, which degrades performance.
Thus, the floating point data type is better compensated.
a source to share