dp.cx blog

MySQL has historically been rather loose with data comparisons. While it has improved, this doesn't feel much different.

In our table is a JSON column that sets the user's stage within the system, and we allow custom filters to be built to pull data from it. This means that our users could put in strings, numbers, comma separated strings, etc. Unfortunately, MySQL's comparison with JSON fields seems to have some issues:

CREATE TABLE a (b JSON);
INSERT INTO a (b) VALUES ('{ "stage": "300" }');

SELECT * FROM a WHERE b->'$.stage' = "300";
+------------------+
| b                |
+------------------+
| {"stage": "300"} |
+------------------+
1 ROW IN SET (0.00 sec)

SELECT * FROM a WHERE b->'$.stage' IN ("300");
+------------------+
| b                |
+------------------+
| {"stage": "300"} |
+------------------+
1 ROW IN SET (0.00 sec)

SELECT * FROM a WHERE b->'$.stage' = 300;
Empty SET (0.00 sec)

SELECT * FROM a WHERE b->'$.stage' IN (300);
Empty SET (0.00 sec)

SELECT * FROM a WHERE b->'$.stage' IN ("300", "350");
Empty SET, 1 warning (0.00 sec)

SHOW warnings;
+---------+------+-----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                           |
+---------+------+-----------------------------------------------------------------------------------+
| Warning | 1235 | This version OF MySQL doesn't yet support 'comparison OF JSON IN the IN operator' |
+---------+------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

SELECT * FROM a WHERE b->'$.stage' IN (300, "350");
+------------------+
| b                |
+------------------+
| {"stage": "300"} |
+------------------+
1 ROW IN SET, 1 warning (0.00 sec)

SHOW warnings;
+---------+------+-----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                           |
+---------+------+-----------------------------------------------------------------------------------+
| Warning | 1235 | This version OF MySQL doesn't yet support 'comparison OF JSON IN the IN operator' |
+---------+------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Notice that an = and IN comparison with a string returns the expected results, but a single digit value does not. However, an IN with two string values returns no results and throws a warning, but a digit and string value returns expected results but also a warning.

Our solution? Take any digits that a user has input to the system and pass them to MySQL as both digits and strings when using an IN/NOT IN.