CODEWITHSUNDEEP
mysql
nfuria
nfuria

Reputation: 71

Unexpected result of mysql select where

In an php application, I did a query based on the result of other query. The first query lists some rows, each one identified by a id and the second query uses this id to select the results.

I used a code in the first query that group_concat the ids but I forgot to resolve these ids and - for my surprise - the second query worked, selecting the first id of this group_concat result.

It works independent of the separator between the ids.

Is this the expected behavoir? 

mysql> select * from parts;
+--------+-------------------------------+---------+
| partid | partname                      | partabr |
+--------+-------------------------------+---------+
|     70 | Ring and pinion               | CP      |
|     71 | Complete differential case    | CDC     |
|     72 | Empty differential case       | CDV     |
|     73 | Differential case repair kit  | KCD     |
|     74 | Shim kit                      | KC      |
|     75 | Differential case bearing kit | KRC     |
|     76 | Pinion gear bearing kit       | KRP     |
|     77 | Axle shaft assembly           | SEC     |
|     78 | Axle shaft                    | SE      |
|     79 | Axle shaft repair kit         | KSE     |
|     80 | Differential end fitting      | NULL    |
|     81 | End yoke                      | TD      |
|     82 | Companion flange              | FAD     |
+--------+-------------------------------+---------+
13 rows in set (0.00 sec)

mysql> select * from parts where partid = '78,72';
+--------+------------+---------+
| partid | partname   | partabr |
+--------+------------+---------+
|     78 | Axle shaft | SE      |
+--------+------------+---------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from parts where partid = '78 # 72';
+--------+------------+---------+
| partid | partname   | partabr |
+--------+------------+---------+
|     78 | Axle shaft | SE      |
+--------+------------+---------+
1 row in set, 1 warning (0.00 sec)

Upvotes: 0

Views: 51

Answers (1)

Dylan Su
Dylan Su

Reputation: 6065

As expected.

Here is why:

(MySQL will try its best to treat '78,72' as a integer)

mysql> select '78,72' = 78, '78 # 72' = 78;
+--------------+----------------+
| '78,72' = 78 | '78 # 72' = 78 |
+--------------+----------------+
|            1 |              1 |
+--------------+----------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '78,72'   |
| Warning | 1292 | Truncated incorrect DOUBLE value: '78 # 72' |
+---------+------+---------------------------------------------+
2 rows in set (0.00 sec)

You can use FIND_IN_SET instead.

select * from parts where FIND_IN_SET(partid, '78,72');

Upvotes: 2

Related Questions