Inconsistent int/varchar cast in WHERE close

Question

I have a database table with an id column (VARCHAR(32)).

I don't understand why this query behaves this way:

SELECT * FROM my_table where `id`=40000;

I have 1 Row returned with the id 4e4a2269774a0032fbca2b4692b560b3

I did some tests and I have the following behaviour

SELECT *, case when (cast(`id` as unsigned)=40000) then '40000' else 'not 40000' end FROM my_table where `id`=40000;

Same row returned with 'not 40000'.

SELECT * FROM my_table where `id`=CAST(40000 as char);

No rows returned.

MySQL version is 8.0.15-commercial.

Why is this row returned, when the id is clearly not the one I asked ? How are the cast and comparison done ?

To reproduce this weird behaviour, here is a fiddle thanks to @MadhurBhaiya https://www.db-fiddle.com/f/mwQ9hUVgDisBBBajCFKVHQ/1

`SELECT id FROM my_table where id = 40000; ` How is the first query possible? `Where id = 40000` it returns `4e4a2269774a0032fbca2b4692b560b3` ..it does not "compute"... — Raymond Nijland, Sep 05 '19 at 09:27
The "real" first query is `select * from my_table where `id`=40000`. But the only interesting column here is `id`. EDIT : there are backticks around id — LP154, Sep 05 '19 at 09:29
*"That's the question I'm asking "* If thats the case this question is unclear.. See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) to improve the question.. And getiing infromation how to provide example data and expected results and how to provide a better (working) user case in a sqlfiddle — Raymond Nijland, Sep 05 '19 at 09:31
I think you did not understand the question. I'm asking why does MySQL return a row with `4e4a2269774a0032fbca2b4692b560b3` when I ask for the row with the id `40000` — LP154, Sep 05 '19 at 09:33
@RaymondNijland the weirdness is indeed happening. Check: https://www.db-fiddle.com/f/mwQ9hUVgDisBBBajCFKVHQ/0 I know that MySQL would have implicitly typecasted `4e4a2269774a0032fbca2b4692b560b3` to `4`. But why is it typecasted to `40000` instead. — Madhur Bhaiya, Sep 05 '19 at 09:33
i didn't say it does not happen @MadhurBhaiya ... mine comments where about that the question is unclearly written as it is.. — Raymond Nijland, Sep 05 '19 at 09:35
@LP154 "why does MySQL return a row with 4e4a2269774a0032fbca2b4692b560b3 when I ask for the row with the id 40000" - you did not asked for a row with value (string) 4000 but (integer) 4000. MySQL implicitly typecasted `id` field to integer for the comparison thus. Now, you should use single quotes around the input parameter, to get the appropriate result. `where id = '40000'` — Madhur Bhaiya, Sep 05 '19 at 09:37
the `4e4` part in`4e4a2269774a0032fbca2b4692b560b3` is most likely the problem as that is scientific notitation.. — Raymond Nijland, Sep 05 '19 at 09:39
@MadhurBhaiya I know this, the question is why does this query behaves this way. When I cast manually 4e4a2269774a0032fbca2b4692b560b3 it returns 4, not 40000 — LP154, Sep 05 '19 at 09:39
@RaymondNijland now look at this: https://www.db-fiddle.com/f/mwQ9hUVgDisBBBajCFKVHQ/1 — Madhur Bhaiya, Sep 05 '19 at 09:40
@MadhurBhaiya look to [this](https://www.db-fiddle.com/f/mwQ9hUVgDisBBBajCFKVHQ/1), like i said the `e` on the second position is most likely the problem as that makes it scientific notitation... — Raymond Nijland, Sep 05 '19 at 09:42
It is the `4e4` that is breaking the cast : https://www.db-fiddle.com/f/mwQ9hUVgDisBBBajCFKVHQ/2 ; it's performing a cast to DECIMAL(5,0) — LP154, Sep 05 '19 at 09:43
running the query provided with `4X4....` returns no results. As an aside, why are you trying to check a int value against a varchar id column, seems most illogical. — Martin, Sep 05 '19 at 09:44
the problem here is multiple.. @Martin `4e4a2269774a0032fbca2b4692b560b3` is a scientific notitation. `a2269774a0032fbca2b4692b560b3` after `4e4` is invalid so it becomes 4 and topicstarter is trusting the implicitly typecasted to much which Madhur pointed to.. — Raymond Nijland, Sep 05 '19 at 09:51
[**This**](https://stackoverflow.com/questions/47166199/best-datatype-to-store-hexidecimal-and-hex-characters-in-the-database) SO Q&A could be valuable reading for the OP. — Martin, Sep 05 '19 at 10:37

LP154 · Accepted Answer · 2019-09-05T10:00:19.150

Found thanks to @RaymondNijland, see https://www.db-fiddle.com/f/mwQ9hUVgDisBBBajCFKVHQ/3

SELECT CAST('4e4a2269774a0032fbca2b4692b560b3' as DECIMAL(5,0));

Returns 40000 because 4e4 = 4*10^4 = 40000, then the other part of the string is ignored.

So this query :

SELECT `id` FROM my_table where `id`=40000;

Seems to be equivalent to this query

SELECT `id` FROM my_table where CAST(`id` as DECIMAL(5,0))=40000;

And the result is somehow logic.

MySQL Documentation explains this :

[...]

If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.

In all other cases, the arguments are compared as floating-point (real) numbers. For example, a comparison of string and numeric operands takes places as a comparison of floating-point numbers.

4e4[INSERT ANYTHING NON NUMERIC HERE]... is compared as a "floating-point (real) numbers" by MySQL, with the value 4e4 (=40000).

tip for the next time... use `SHOW WARNINGS:` after the query always... then you see truncation errors (sometimes) -> https://www.db-fiddle.com/f/mwQ9hUVgDisBBBajCFKVHQ/3 ... i said sometimes as it seams that it not does not show the error/warning for the `DECIMAL(5,0)` cast as that is weird as `a2269774a0032fbca2b4692b560b3` is also ignored/truncated ... — Raymond Nijland, Sep 05 '19 at 10:05

Inconsistent int/varchar cast in WHERE close

1 Answers1