[#MDEV-16462] EXPLAIN FORMAT=JSON produces illegal JSON text

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.

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`)));

  "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

 "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.

[MDEV-16462] EXPLAIN FORMAT=JSON produces illegal JSON text Created: 2018-06-11 Updated: 2023-04-27
Status:	Stalled
Project:	MariaDB Server
Component/s:	Admin statements, JSON, Optimizer
Affects Version/s:	10.1, 10.2.15, 10.2, 10.3
Fix Version/s:	10.4

[MDEV-16462] EXPLAIN FORMAT=JSON produces illegal JSON text Created: 2018-06-11 Updated: 2023-04-27