Bug #98292 | mysql5.7.23 virtual generated column on json with index can't find rows | ||
---|---|---|---|
Submitted: | 20 Jan 2020 7:42 | Modified: | 23 Jan 2020 11:00 |
Reporter: | WANRU GE | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: JSON | Severity: | S2 (Serious) |
Version: | 5.7 | OS: | CentOS |
Assigned to: | CPU Architecture: | Any |
[20 Jan 2020 7:42]
WANRU GE
[23 Jan 2020 11:00]
MySQL Verification Team
Hello WANRU GE, Thank you for the report and test case. Imho this behavior is intentional. Quoting note from Bug #80093: [22 Feb 2016 10:35] Knut Anders Hatlen . The current behavior is intentional. The quotes are kept around the string to ensure that it is still a valid JSON document after the conversion from the JSON type to a string type. Since applications may rely on this behaviour and expect a parsable JSON text to be returned, one must be careful about changing it. The approach suggested in bug#78736, where a shorthand for JSON_UNQUOTE(JSON_EXTRACT(...)) is added, is safer because it doesn't modify existing behavior. See, also related bugs - Bug #78736, Bug #76834, Bug #78464. When I used unquoting extraction operator ->> in the DDL, it works: - bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.29 MySQL Community Server (GPL) Copyright (c) 2000, 2020, 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> drop database if exists bug98292; Query OK, 1 row affected (0.05 sec) mysql> create database bug98292; Query OK, 1 row affected (0.00 sec) mysql> use bug98292; Database changed mysql> CREATE TABLE `m_test` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `user_info` json DEFAULT NULL, -> `names_virtual2` varchar(50) GENERATED ALWAYS AS (user_info->>"$.user_basic_info.name") VIRTUAL, -> `names_virtual3` varchar(50) GENERATED ALWAYS AS (user_info->>"$.user_basic_info.name") STORED, -> PRIMARY KEY (`id`), -> KEY `idx_name3` (`names_virtual3`), -> KEY `idx_name` (`names_virtual2`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO `m_test`(id,user_info) VALUES(35213,'{"educations": [{"school": "学校A", "company": "学校3752028932291", "date_range": "1995-1998"}, {"school": "学校B", "company": "学校37520289322913752028932291", "date_range": "1995-1998"}], "work_exprs": [{"company": "公司3752028932291", "date_range": "2001-2003"}, {"company": "公司37520289322913752028932291", "date_range": "2003-2004"}], "user_basic_info": {"age": "3752028932291", "name": "张三3752028932291", "address": {"city": "上海", "province": "上海"}}}'); Query OK, 1 row affected (0.01 sec) mysql> select * from m_test where names_virtual2='张三3752028932291'\G *************************** 1. row *************************** id: 35213 user_info: {"educations": [{"school": "学校A", "company": "学校3752028932291", "date_range": "1995-1998"}, {"school": "学校B", "company": "学校37520289322913752028932291", "date_range": "1995-1998"}], "work_exprs": [{"company": "公司3752028932291", "date_range": "2001-2003"}, {"company": "公司37520289322913752028932291", "date_range": "2003-2004"}], "user_basic_info": {"age": "3752028932291", "name": "张三3752028932291", "address": {"city": "上海", "province": "上海"}}} names_virtual2: 张三3752028932291 names_virtual3: 张三3752028932291 1 row in set (0.00 sec) mysql> select * from m_test where names_virtual3='张三3752028932291'\G *************************** 1. row *************************** id: 35213 user_info: {"educations": [{"school": "学校A", "company": "学校3752028932291", "date_range": "1995-1998"}, {"school": "学校B", "company": "学校37520289322913752028932291", "date_range": "1995-1998"}], "work_exprs": [{"company": "公司3752028932291", "date_range": "2001-2003"}, {"company": "公司37520289322913752028932291", "date_range": "2003-2004"}], "user_basic_info": {"age": "3752028932291", "name": "张三3752028932291", "address": {"city": "上海", "province": "上海"}}} names_virtual2: 张三3752028932291 names_virtual3: 张三3752028932291 1 row in set (0.00 sec) regards, Umesh