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:
None 
Category:MySQL Server: JSON Severity:S2 (Serious)
Version:5.7 OS:CentOS
Assigned to: CPU Architecture:Any

[20 Jan 2020 7:42] WANRU GE
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)
[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