0

I am studying a bit of MySQL and I have found something relatively strange. I have a table with certain columns, one is id, which is a number. At some point a query like this: SELECT name,surname,etc from table where id='1 thousand' came in. It behaves exactly as if the value to compare was just 1. So, I read the docs about implicit conversion and did not find the specific rules about this conversion. I did some testing and found that, apparently, when converting a string to a number the conversion stops as soon as a non-numeric character is found. I am pretty certain about this because of my testing. However I would like to confirm it properly. Can anyone point to a place where this is documented? The closest I got to it was the line:

The reason for this is that there are many different strings that may convert to the value 1, such as '1', ' 1', or '1a'.

in: https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html Which does mention it, but does not defines the exact rules for it. I am guessing that spaces are removed and conversion stops at the first non-numeric character. Someone has insight on where to find these rules?

The mentioned question in the closure reason asks about whether or not certain values are converted to 1:

'1234'=1234 ?
'1abc' = 1 ?
'text' = 1 ?

My question is not about which values are converted to X or Y. I already know by experimentation and reading some of that, as I understand implicit conversion. My question is about the rules that define the implicit conversion. Like stated above, I have certain suspicions about these rules, but I don't find a documentation that covers this topic. The linked resource in the accepted answer in the other question is the same I included in the question, and does note explain the conversion rules, which is what is being asked in this question.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Ilab Sentuser
  • 25
  • 5
  • *It behaves exactly as it the value to compare was just 1.* No. This depends on current SQL Mode and may either produce an error or truncate the value like you describe. – Akina Nov 07 '22 at 12:19
  • The casting rules are complex and also fairly lax. The bigger question to ask is how you ended up with `1a` in a column of numeric values. – Tim Biegeleisen Nov 07 '22 at 12:22
  • @TimBiegeleisen is mainly for educative purposes, and is related to SQL injections, so you can end up with very strange things happening sometimes :). And while I know about the implicit conversion I still can't find the exact rules about it :( – Ilab Sentuser Nov 10 '22 at 14:12
  • The rules are actually in the MySQL official documentation, buried somewhere. Your question is not a bad one, though perhaps is already covered elsewhere on this site. – Tim Biegeleisen Nov 10 '22 at 14:15
  • @Akina I understand your point, however in this case it does behaves like stated, as it came from real testing. I do not know which mode it is, but it did behaved exactly in that way. So it did not produce an error, but maybe truncated the value, which is what i want to know. Where are the rules about this truncation. – Ilab Sentuser Nov 10 '22 at 14:24
  • @TimBiegeleisen I do think that this is probably in the docs, but if it is, it is, like you said, buried, as I couldn't find it at all. Neither here on this site. I am not saying it is not, but after looking for a considerable time I decided to ask instead. Maybe someone with enough experience in the field knows the answer or where to find the actual documentation. In the meantime of course, I keep looking :) – Ilab Sentuser Nov 10 '22 at 14:28
  • https://stackoverflow.com/questions/46235729/implicit-conversion-of-a-numeric-in-mysql – Tim Biegeleisen Nov 10 '22 at 14:35
  • @TimBiegeleisen I already checked that question, and the link to the docs. The link provided in the accepted answer is the same that I provided here (only changing the version). It states the rules about different types and gives several examples about them, but does not cover the rules of STRING to INTEGER, it does covers the rules for the other way around though. However, I will check it out and see if I find anything different from what I already read on the 8.0 version, although at a first glance this section of the docs does not seem to have changed between such versions. – Ilab Sentuser Nov 10 '22 at 14:50

0 Answers0