Search code examples
mysqlnumbersquotes

Why do the single quotes in this SQL query affect the calculations?

SELECT COUNT(*) FROM planets
WHERE ROUND(SQRT(POWER(('71' - coords_x), 2) +
                 POWER(('97' - coords_y), 2))) <= 17

==> 51

SELECT COUNT(*) FROM planets
WHERE ROUND(SQRT(POWER((71 - coords_x), 2) +
                 POWER((97 - coords_y), 2))) <= 17

==> 22

coords_x and coords_y are both TINYINT fields containing values in the range [1, 100]. Usually MySQL doesn't care if numbers are quoted or not.. but apparently it does in this case. The question is: Why?

Solution

  • The implicit conversion from string to floating point number is probably causing in inaccurate results. See: Type Conversion in Expression Evaluation