Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1, 10.2.15, 10.2, 10.3
-
None
Description
EXPLAIN FORMAT=JSON can produce JSON with illegal content, that is, JSON that cannot be parsed correctly. This happens when some BLOB value ends up in an "attached_condition" for a "duplicate_removal". The bytes of the BLOB seem to be inserted directly into the JSON string without any conversion to text. I would expect some proper conversion to, for instance, hexadecimal notation or Base64. Chances are high that the bytes of the BLOB will not be proper Unicode characters in the selected character encoding (UTF-8 in my case) and therefore a proper JSON parser will reject the JSON value.
This behavior can be reproduced using the following script:
CREATE TABLE `table1` ( |
`ID` int(11) NOT NULL AUTO_INCREMENT, |
`AddID` int(11) NOT NULL, |
PRIMARY KEY (`ID`,`AddID`) |
) ENGINE=InnoDB AUTO_INCREMENT=94 DEFAULT CHARSET=utf8; |
|
INSERT INTO `table1` VALUES (1,17),(1,103),(2,17),(2,103),(3,17),(3,103),(4,17),(4,103),(5,17),(5,103),(6,17),(6,103),(7,17),(7,103),(8,17),(8,103),(9,17),(9,103),(10,17),(10,103),(11,17),(11,103),(12,28),(12,103),(12,107),(13,31),(13,40),(13,103),(14,32),(14,103),(15,33),(15,103),(16,34),(16,106),(17,35),(17,103),(18,36),(18,103),(19,41),(19,103),(19,112),(20,45),(20,103),(20,127),(20,234),(21,46),(21,103),(22,47),(22,103),(23,48),(23,103),(24,49),(24,103),(25,50),(25,59),(25,108),(25,135),(25,175),(26,51),(26,58),(26,103),(27,52),(27,57),(27,103),(28,53),(28,56),(28,103),(29,54),(29,55),(29,109),(29,129),(29,134),(29,232),(29,233),(30,60),(30,103),(30,128),(30,229),(31,61),(31,103),(31,126),(32,62),(32,103),(32,125),(33,81),(33,103),(33,124),(34,88),(34,103),(34,123),(35,99),(35,103),(36,113),(37,216),(37,217),(38,219),(39,222),(40,223),(41,224),(42,231),(43,337),(44,338),(45,340),(46,428),(47,429),(48,466),(48,469),(48,470),(48,471),(48,550),(49,533),(50,535),(51,556),(52,587),(53,588),(54,593),(55,594),(56,596),(57,597),(58,599),(60,602),(61,608),(62,609),(63,610),(64,611),(65,612),(66,612),(67,615),(68,616),(69,617),(70,617),(71,617),(72,618),(73,618),(74,618),(75,619),(76,620),(77,621),(78,621),(79,623),(80,624),(81,624),(82,625),(83,737),(84,746),(85,944),(86,944),(87,949),(88,994),(89,1129),(90,1130),(91,1131),(92,1132),(93,1133); |
|
CREATE TABLE `table2` ( |
`TransactionID` int(11) NOT NULL, |
`EntryID` int(11) NOT NULL, |
PRIMARY KEY (`TransactionID`,`EntryID`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
|
INSERT INTO `table2` VALUES (1,50),(2,45),(3,65),(4,66),(5,90),(6,91),(7,92),(9,89),(26,88),(37,67),(142,68),(243,57),(244,56),(245,58),(246,60),(247,84),(248,61),(249,63),(250,62),(251,64),(990,85),(1055,86),(2065,87); |
|
CREATE TABLE `table3` ( |
`TransactionId` int(11) NOT NULL, |
`ObjectId` binary(16) NOT NULL, |
PRIMARY KEY (`TransactionId`,`ObjectId`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
|
INSERT INTO `table3` VALUES (5,0x02697732663011E39AE220CF3068F21D),(6,0x02697732663011E39AE220CF3068F21D),(9,0x02697732663011E39AE220CF3068F21D),(37,0x7FD4C464D6BC4049A6FBCBE97067B32D),(243,0x1E34A871555442AF92F0D602F78F6D02),(244,0x6F75F8B7F91E4820BA773A6AE1FE90B4),(244,0xD42B67CA12C942B5A0A777BA6CB8857A),(245,0xE07DC04E70284E68B1ABE55002BB0C49),(246,0xE07DC04E70284E68B1ABE55002BB0C49),(248,0xE07DC04E70284E68B1ABE55002BB0C49),(249,0x41803CA4A50511E3A08B20CF3068F21D),(250,0x41803CA4A50511E3A08B20CF3068F21D),(251,0x3F2DEF327D1743F08DBDAE5A7B203200),(251,0xDEF3F686DB2D46C4BF67C33807425A19); |
|
CREATE TABLE `table4` ( |
`ID` int(11) NOT NULL AUTO_INCREMENT, |
`Description` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, |
`Uuid` binary(16) NOT NULL, |
PRIMARY KEY (`ID`) |
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; |
|
INSERT INTO `table4` VALUES (1,'Test1',0x41803CA4A50511E3A08B20CF3068F21D),(2,'Test2',0xDEF3F686DB2D46C4BF67C33807425A19),(3,'Test3',0x3F2DEF327D1743F08DBDAE5A7B203200); |
|
EXPLAIN FORMAT=JSON SELECT `table1`.`ID` FROM `table1` WHERE (3 IN (SELECT `table4`.`ID` FROM `table4`, `table2`, `table3` WHERE (`table2`.`EntryID` = `table1`.`ID`) AND (`table3`.`TransactionId` = `table2`.`TransactionID`) AND (`table4`.`Uuid` = `table3`.`ObjectId`))); |
This should produce something like:
{
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "table4",
|
"access_type": "const",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["ID"],
|
"ref": ["const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"duplicates_removal": {
|
"table": {
|
"table_name": "table3",
|
"access_type": "index",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "20",
|
"used_key_parts": ["TransactionId", "ObjectId"],
|
"rows": 14,
|
"filtered": 78.571,
|
"attached_condition": "table3.ObjectId = '?-?2}C????Z{ 2\0'",
|
"using_index": true
|
},
|
"table": {
|
"table_name": "table2",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["TransactionID"],
|
"ref": ["test_bug.table3.TransactionId"],
|
"rows": 1,
|
"filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "table1",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["ID"],
|
"ref": ["test_bug.table2.EntryID"],
|
"rows": 1,
|
"filtered": 100,
|
"using_index": true
|
}
|
}
|
}
|
}
|
Please note the line:
"attached_condition": "table3.ObjectId = '?-?2}C????Z{ 2\0'"
|
The resulting JSON has been copied from the mysql command client and seems to have already been processed for illegal characters in some way. The underlying client/server protocol does return the bytes of the BLOB as is.
Attachments
Issue Links
- relates to
-
MDEV-25770 ANALYZE FORMAT=JSON $stmt - invalid JSON
- Open