| Bug #110854 | mysql JSON_SEARCH The integer type cannot be searched, but the character type ha | ||
|---|---|---|---|
| Submitted: | 28 Apr 2023 1:57 | Modified: | 4 May 2023 11:43 |
| Reporter: | chen chen | Email Updates: | |
| Status: | Unsupported | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.7.40-log | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[2 May 2023 13:15]
MySQL Verification Team
Hi Mr. chen, Thank you for your bug report. However, we are not able to repeat it. We need the entire test case, including all the tables, settings etc ...... The other reasons why we can't repeat what you are reporting is that we simply accept reports only in English language. Can't repeat.
[4 May 2023 1:46]
chen chen
Thanks Reply!
I have provided the relevant table structure, related SQL statements, and query results. The test can reproduce the problem in other 5.7 versions of mysql. If you need other information, I will be happy to provide it.
The sql information is as follows
-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`k` int(10) NOT NULL DEFAULT 0 AUTO_INCREMENT,
`data` json NULL,
PRIMARY KEY (`k`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES (1, '[{\"id\": 1, \"name\": \"a\", \"score\": 4.6}, {\"id\": 2, \"name\": \"b\", \"score\": 4.2}, {\"id\": 3, \"name\": \"c\", \"score\": 4.3}, {\"id\": 4, \"name\": \"d\", \"score\": 4.4}, {\"id\": 5, \"name\": \"e\", \"score\": 4.5}]');
INSERT INTO `test` VALUES (2, '[{\"id\": 1, \"name\": \"a\", \"score\": 4.9}, {\"id\": 2, \"name\": \"b\", \"score\": 4.1}, {\"id\": 3, \"name\": \"c\", \"score\": 3.6}, {\"id\": 4, \"name\": \"d\", \"score\": 4.6}, {\"id\": 5, \"name\": \"e\", \"score\": 3.7}]');
Check for phrases
mysql> SELECT k,JSON_SEARCH(`data`,'one',"a",NULL,'$[*].name') AS `Can be queried`,JSON_SEARCH(`data`,'one',1,NULL,'$[*].id') AS `Unable to find`,JSON_SEARCH(`data`,'one','1',NULL,'$[*].id') AS `can not query` FROM `test`;
+---+----------------+----------------+---------------+
| k | Can be queried | Unable to find | can not query |
+---+----------------+----------------+---------------+
| 1 | "$[0].name" | NULL | NULL |
| 2 | "$[0].name" | NULL | NULL |
+---+----------------+----------------+---------------+
2 rows in set (0.00 sec)
The expected result is that the integer data can be returned normally when searching. Currently, the null returned by the integer type will also return null when trying to match the string. The guess is strictly matched according to the type, and the string search is normally returned.
[4 May 2023 11:43]
MySQL Verification Team
Hi Mr. chen, Sorry, but MyISAM SE is no longer maintained. Also, 5.7 is receiving only fixes for the crashing and security bugs.
Description: JSON_SEARCH在搜索json内是整型的无法搜索，字符型无问题 相关的内容 data字段值为 [{"id": 1, "name": "教学严谨", "score": 5}, {"id": 2, "name": "态度亲和", "score": 5}, {"id": 3, "name": "仪表端正", "score": 5}, {"id": 4, "name": "互动教学", "score": 5}, {"id": 5, "name": "耐心指导", "score": 5}] SELECT JSON_SEARCH(data, 'one',"教学严谨",NULL , '$[*].name') AS `可以查询`, JSON_SEARCH(data, 'one',1,NULL , '$[*].id') AS `查询不到`, JSON_SEARCH(data, 'one','1',NULL , '$[*].id') AS `也查询不到`, FROM `table_name` LIMIT 5 How to repeat: 已在上面说明 Suggested fix: 支持整型的查找，字符串匹配性能低