MySQL Bugs: #93800: Inconsistent JSON unquoting with WHERE IN

Bug #93800	Inconsistent JSON unquoting with WHERE IN
Submitted:	3 Jan 2019 15:01	Modified:	3 Jan 2019 17:17
Reporter:	Jonas Staudenmeir	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S3 (Non-critical)
Version:	8.0.12	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

Description:
Using WHERE IN with JSON strings:

With a single value, it behaves like a WHERE clause and automatically unquotes the JSON string (see queries #1 and #2).

With multiple values, it behaves differently and doesn't unquote the JSON string (see query #3). Here, the WHERE IN clause only works with JSON values (see query #4).

How to repeat:
Fiddle: https://www.db-fiddle.com/f/3nVRZDzpjaL2L2DTTSN6ew/0

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `options` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `users` (`id`, `options`) VALUES
(1,	'{\"locale\": \"en\"}'),
(2,	'{\"locale\": \"de\"}');

select * from users where json_extract(options, '$.locale') = 'en'; # query 1

select * from users where json_extract(options, '$.locale') in ('en'); # query 2

select * from users where json_extract(options, '$.locale') in ('en', 'de'); # query 3

select * from users where json_extract(options, '$.locale') in ('"en"', '"de"'); # query 4

Thank you for the bug report. Have you noticed the warning in query 3?.

C:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > " --default-character-set=utf8mb4
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.15 Source distribution BUILD: 2018-DEC-11

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 8.0 > USE test
Database changed
mysql 8.0 > CREATE TABLE `users` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `options` json NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.03 sec)

mysql 8.0 > INSERT INTO `users` (`id`, `options`) VALUES
    -> (1,'{\"locale\": \"en\"}'),
    -> (2,'{\"locale\": \"de\"}');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 8.0 > select * from users where json_extract(options, '$.locale') = 'en'; # query 1
+----+------------------+
| id | options          |
+----+------------------+
|  1 | {"locale": "en"} |
+----+------------------+
1 row in set (0.00 sec)

mysql 8.0 > select * from users where json_extract(options, '$.locale') in ('en'); # query 2
+----+------------------+
| id | options          |
+----+------------------+
|  1 | {"locale": "en"} |
+----+------------------+
1 row in set (0.00 sec)

mysql 8.0 > select * from users where json_extract(options, '$.locale') in ('en', 'de'); # query 3
Empty set, 1 warning (0.00 sec)

mysql 8.0 > 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)

mysql 8.0 > select * from users where json_extract(options, '$.locale') in ('"en"', '"de"'); # query 4
+----+------------------+
| id | options          |
+----+------------------+
|  1 | {"locale": "en"} |
|  2 | {"locale": "de"} |
+----+------------------+
2 rows in set, 1 warning (0.00 sec)

mysql 8.0 >

No, I didn't know about "show warnings". So it's basically not a bug?

Thank you for the feedback. According the warning message currently unsupported.