Does MySQL automatically casting\converting the string to numeric value?
How does that conversion works?
Given that units.id is of bigint type, how this query will be interpreted?
SELECT table.*
FROM table
WHERE id='text'
The answers to your first three questions are: yes, yes, and no.
When the string 'text' is converted to a number, it becomes the value 0.
The documentation that describes type conversion is here.
For your query:
SELECT table.*
FROM table
WHERE id='text';
The rule is captured by this excerpt from the documentation:
In all other cases, the arguments are compared as floating-point (real) numbers.
In other words, this is actually equivalent to:
WHERE id = 0.0