Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: sql 查询错误
|
username: rw12306
The SQL query is inaccurate and retrieves other data based on the conditions.
|
username: rw12306
|
username: Your MPI field is not init, right?
Values of non-init fields need to be enclosed in quotes. Normally, your SQL should have reported an error. TiDB might not have recognized it and displayed all the data.
For fields that are init, changing MPI to 0 is fine; but for non-init MPI types, you need to use MPI=‘0’.
|
username: Normally, it won’t report an error. It’s the same with MySQL. It’s best to enclose string types in quotes.
|
username: This question is about how cast is implemented. In the execution plan of your SQL, MPI will be implicitly converted, and if the first character is not 0 to 9, it will take 0, so most of the results are those starting with letters. For more details, you can check:
|
username: There won’t be an error, but this is indeed a problem, it should not return data.
No problem, when converting a string to an integer, if the first character is not a digit, the result will be 0.
You can test it out:
select CONVERT('abcd',SIGNED);
select CONVERT('2bcd',SIGNED);
|
username: 
|
username: Are you trying to find rows where the mpi column is empty? Try changing the query condition to mpi is null.
|
username: MySQL has the same issue. It traverses the string from left to right. If the leftmost character is a sign or a digit, it continues; otherwise, it is considered empty and assigned a value of 0.
Actually, the original poster just needs to add quotation marks.
|
username: Damn, I’ll go try it. Indeed, it’s still according to the type of query. Generally, it’s impossible to pass an int to a varchar type, right?
|
username: This topic was automatically closed 1 minute after the last reply. No new replies are allowed.