SQL Query return match for id 3 with where = 3XW3Z1zD6JEV1600164577

Question

I have this query

SELECT u0_.id AS id_0, u0_.uid AS uid_1, u0_.username AS username_2
FROM user u0_
WHERE u0_.uid = '3XW3Z1zD6JEV1600164577' OR u0_.id = '3XW3Z1zD6JEV1600164577';

that return two results:

user with id 3

user with uid 3XW3Z1zD6JEV1600164577

I tried using sequel pro to confirm and still get wrong result:

If I remove the first 3 from the uid like this XW3Z1zD6JEV1600164577 I get no match. so why is behaving like a LIKE ??

I'd guess it's `u0_.id = '3XW3Z1zD6JEV1600164577'` converting the right hand side into integer 3. Which database is this? What were you trying to achieve with that if ID is an integer column? — Rup, Oct 28 '21 at 13:05
it is mariaDB; I am trying to match a user with its ID or UID depending on the data passed — Sam, Oct 28 '21 at 13:07

Salman A · Accepted Answer · 2021-10-28T13:26:53.140

You are comparing an number with a string in which case MySQL will convert both operands to float:

For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers.

MySQL is also very forgiving when converting string to numbers:

SELECT CAST('3X' AS FLOAT) -- 3
SELECT CAST('X3' AS FLOAT) -- 0

This explains why it is matching id = 3. So either don't compare different datatypes or convert the integer to string to get expected result:

WHERE u0_.uid = '3XW3Z1zD6JEV1600164577'
OR    CAST(u0_.id AS CHAR) = '3XW3Z1zD6JEV1600164577'

SQL Query return match for id 3 with where = 3XW3Z1zD6JEV1600164577

1 Answers1