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: | |
Category: | MySQL Server: JSON | Severity: | S3 (Non-critical) |
Version: | 8.0.12 | OS: | Ubuntu |
Assigned to: | CPU Architecture: | Any |
[3 Jan 2019 15:01]
Jonas Staudenmeir
[3 Jan 2019 15:21]
MySQL Verification Team
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 >
[3 Jan 2019 16:25]
Jonas Staudenmeir
No, I didn't know about "show warnings". So it's basically not a bug?
[3 Jan 2019 17:17]
MySQL Verification Team
Thank you for the feedback. According the warning message currently unsupported.