| 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 | |
[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
Description: i have a table with json column in mysql5.7.23, i created virtual generated column on that column, and i added a index on virtual generated column,but when i select record with that virtual generated column, it returned Empty set, when i select record with json column ,it returned one rows. How to repeat: Create Table: CREATE TABLE `m_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_info` json DEFAULT NULL, `names_virtual2` varchar(50) GENERATED ALWAYS AS (json_extract(json_extract(`user_info`,'$.user_basic_info'),'$.name')) VIRTUAL, `names_virtual3` varchar(50) GENERATED ALWAYS AS (json_extract(json_extract(`user_info`,'$.user_basic_info'),'$.name')) STORED, PRIMARY KEY (`id`), KEY `idx_name3` (`names_virtual3`), KEY `idx_name` (`names_virtual2`) ) ENGINE=InnoDB AUTO_INCREMENT=21039616 DEFAULT CHARSET=utf8 mysql> select * from m_test where names_virtual2='张三3752028932291'; Empty set (0.00 sec) mysql> select * from m_test where names_virtual3='张三3752028932291'; Empty set (0.00 sec) mysql> select * from m_test where json_extract(json_extract(`user_info`,'$.user_basic_info'),'$.name')='张三3752028932291'; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+-----------------------+ | id | user_info | names_virtual2 | names_virtual3 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+-----------------------+ | 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": "上海"}}} | "张三3752028932291" | "张三3752028932291" | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+-----------------------+ 1 row in set (1 min 28.49 sec)