0

I have a database query where I am passing in a string to select where either an int (id) or string (name) match.

For example:

id (int) name (varchar)
13 this_is_a_name

I am passing in because I want to be able to find in either case.

The problem is, when I execute the following query, which should return nothing:

SELECT * FROM items 
WHERE 
name = '13ff66201c8814f568bd1e5ba304486a' 
OR 
id = '13ff66201c8814f568bd1e5ba304486a' 
ORDER BY id ASC LIMIT 1;

I get a match on the id when I should not be returning rows. Can somebody shed light on why this is happening? I get it's some sort of string to int issue given the first two characters of the string are 13 but it seems like the wrong behaviour.

I'm doing it this way because I need at a key point to identify if I'm dealing with a particular data type when the data type hasn't previously been linked through a linking table.

Gui
  • 165
  • 8
Deggy
  • 36
  • 4
  • i didn't find the mariadb docs which say that's occurring, however i saw the mysql docs for it here: https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html if that's the case, i guess the solution would have to be something like: `CAST(id as STRING) = ...` and I get why you're saying this is wrong behaviour. – mechanical_meat Dec 22 '21 at 00:22
  • 1
    Thank you. ```CAST(id AS CHAR)``` did the trick. – Deggy Dec 22 '21 at 00:40
  • 1
    I don't know about mariadb but I would be careful about a cast to char without specifying length. In Teradata this would default to CHAR(1) (and the result is '1' for your example), and in SQL Server it defaults to char(30), and your result is truncated. So use CAST(Id as VARCHAR(50)) or something like that (if the ID length is always under 50 chars) – tinazmu Dec 22 '21 at 02:11

0 Answers0