marpeta

Atsiprašau, mielieji lietuviškieji skaitytojai, bet šitas įrašas yra skirtas specifinei vartotojų grupei, todėl bus parašytas anglų kalba. Ačiū ir iki :)

---

I just want to mention really simple issue with set of values in MySQL5 because I managed to get trapped by this simple issue for couple of hours trying to understand why the result in couple of lines of code is so weird. When I managed to figure it out I sent this simple trick to couple of colleagues and they couldn’t come with the solution in a matter of minutes so I thought that it would be interesting to mention it and let google index it for others.

To make this short, simple and interesting let’s try playing the game.

If there was “What Geek Wants to be a Millionaire” then this could go for the last question. You know the pressure and all could take couple of minutes to figure the answer out :)

Questions

So, here it goes. We have three simple MySQLv5 queries executed on default MySQL configuration. You have to give the result for these three simple queries.

1:

SELECT ('mysql' IN (0)) AS '@result';

2:

SELECT ('mysql' IN (1)) AS 'result';

3:

SELECT ('mysql' IN ('sql')) AS result;

If you know the answer so you could find the trick very quickly and you’re the winner of “Who Wants to be a Millionaire” :)

Answers

The answers are quite simple as queries. Here it goes:

1. The problem with the first query is that ‘mysql’ is of string type and 0 is numeric and there has to be type casting. So string ‘mysql’ is casted into numeric 0 and now we have query where we want to find 0 in set of only one value and that value is 0:

SELECT (0 IN (0))

So the result will be 1:

mysql> SELECT ('mysql' IN (0)) AS '@result';+---------+| @result |+---------+|       1 |+---------+1 row in set, 1 warning (0.01 sec)

mysql> SHOW WARNINGS;+---------+------+-------------------------------------------+| Level   | Code | Message                                   |+---------+------+-------------------------------------------+| Warning | 1292 | Truncated incorrect DOUBLE value: 'mysql' |+---------+------+-------------------------------------------+1 row in set (0.00 sec)

2. As I explained in the first example the query could be translated into:

SELECT (0 IN (1))

So the result will be 0.

3. This query is just for those newbies that don’t know MySQL’s IN() :) The answer unquestionably is 0.

So the first query was with the trick and I hope that there would be couple of people that will save ton of time trying to debug some similar query to first example.