| 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 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 >
Description: Create a table with a JSON column. Performing a query for "WHERE column IN (0)" returns the correct results, but adding any other value to the IN expression (even "IN (0, 0)") will return non-matching values. Reproduced on MySQL 5.7.35; not present in 8.0.26. How to repeat: Execute the following DDL: create schema test collate utf8_general_ci; create table test.data(value JSON); insert into test.data(value) values('0'),(null),('1'),('2'),('{"prop":1}'),('"test"'),('""'); Execute this: SELECT value FROM data WHERE value IN (0); It returns the one expected row. Execute this: SELECT value FROM data WHERE value IN (0, 0); It returns four rows: 0 {"prop": 1} "test" "" This only happens for 0; "WHERE value IN (1, 2)" works as expected. Suggested fix: Only the first row should be returned for the second query.