What complications can occur when run a query with quoted number (string) on integer field in MySQL

Question

In SQL you should not quote integer numbers, because if you quote, it would be a string.

But i'm curious of what problems/complications can occur if I do so?

For example:
SELECT * FROM table WHERE id = 1 (Correct)
vs.
SELECT * FROM table WHERE id = '1' (Incorrect)

P.s. Didn't find any duplicate on this question. Correct me if there is

Answer 1

This is an interesting question, and I spent A LOT of time investigating the possible outcome (for mysql).

So far I was able to find only 1,5 disadvantages:

• First, you will get weird results if perform a math or a comparison operation on a BIGINT value if one of operands is sent in the query as a string - due to the fact that in this case both operands will be cast to floats and thus lose precision. Here is a demonstration code. Just run these queries and check the results, which are quite embarrassing:

  create table bint(i bigint);
  insert into bint values (18014398509481984);
  update bint set i=i+'1';
  update bint set i=i+1
  update bint set i=i+'1'
  

  But for just selecting or updating BIGINT values there is still no problem to have them quoted in the query or bound as strings in the prepared statement.

• Second I count as only a half of issue, as I am still unable to find a good proof. But DBAs insist that there are some mystic queries which are so complex that optimizer can be spoiled by the wrong data type and will choose the wrong execution plan. Yet in my 15+ years of experience I didn't have a luck of finding one. I'll put the max bounty to the answer that will be able to provide a reproduceable proof, not just a tale of the good old times.

So you can tell that for the regular queries with regular data types there is absolutely no difference.

The only query part that is syntactically doesn't allow string operands is a LIMIT clause: a LIMIT '1' will cause a syntax error.

However, with a prepared statement, if you bind a LIMIT parameter as a string, it will do all right:

$stmt = $mysqli->prepare("SELECT value FROM table LIMIT ?");
$stmt->bind_param("s", $limit);

will go without errors.