Bug #104816 | "IN (0, ...)" on a JSON column returns non-matching values | ||
---|---|---|---|
Submitted: | 2 Sep 2021 18:57 | Modified: | 2 Sep 2021 22:52 |
Reporter: | Bradley Grainger (OCA) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S2 (Serious) |
Version: | 5.7.35 | OS: | Linux (mysql:5.7.35 Docker) |
Assigned to: | CPU Architecture: | Any |
[2 Sep 2021 18:57]
Bradley Grainger
[2 Sep 2021 22:52]
MySQL Verification Team
Thank you for the bug report. 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.27 Source distribution BUILT: 2021-SEP-01 Copyright (c) 2000, 2021, Oracle and/or its affiliates. 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 > create schema test collate utf8_general_ci; Query OK, 1 row affected, 1 warning (0.01 sec) mysql 8.0 > create table test.data(value JSON); Query OK, 0 rows affected (0.04 sec) mysql 8.0 > insert into test.data(value) values('0'),(null),('1'),('2'),('{"prop":1}'),('"test"'),('""'); Query OK, 7 rows affected (0.02 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql 8.0 > USE test Database changed mysql 8.0 > SELECT value FROM data WHERE value IN (0); +-------+ | value | +-------+ | 0 | +-------+ 1 row in set (0.01 sec) mysql 8.0 > SELECT value FROM data WHERE value IN (0, 0); +-------+ | value | +-------+ | 0 | +-------+ 1 row in set (0.00 sec) ===================================================================================================== c:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --prompt="mysql 5.7 > " Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.36-log Source distribution BUILT: 2021-SEP-01 Copyright (c) 2000, 2021, Oracle and/or its affiliates. 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 5.7 > DROP DATABASE test; Query OK, 3 rows affected (0.03 sec) mysql 5.7 > create schema test collate utf8_general_ci; Query OK, 1 row affected (0.01 sec) mysql 5.7 > create table test.data(value JSON); Query OK, 0 rows affected (0.03 sec) mysql 5.7 > insert into test.data(value) values('0'),(null),('1'),('2'),('{"prop":1}'),('"test"'),('""'); Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql 5.7 > USE test Database changed mysql 5.7 > SELECT value FROM data WHERE value IN (0); +-------+ | value | +-------+ | 0 | +-------+ 1 row in set (0.00 sec) mysql 5.7 > SELECT value FROM data WHERE value IN (0, 0); +-------------+ | value | +-------------+ | 0 | | {"prop": 1} | | "test" | | "" | +-------------+ 4 rows in set, 3 warnings (0.00 sec) mysql 5.7 >