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

Details

Type: Bug
Status: Stalled (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.2.15, 10.1(EOL), 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.4(EOL)
Component/s: Admin statements, JSON, Optimizer
Labels:
None

Description

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

Activity

Ascending order - Click to sort in descending order

Peter-Jan Roes created issue - 2018-06-11 15:04

Peter-Jan Roes made changes - 2018-06-11 15:08

Field	Original Value	New Value
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: {code:sql} 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`))); {code} This should produce something like: {code:json} { "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 } } } } {code}	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: {code:sql} 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`))); {code} This should produce something like: {code:json} { "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 } } } } {code} Please note the line: {code} "attached_condition": "table3.ObjectId = '?-?2}C????Z{ 2\0'" {code} The resulting JSON has been copied from the mysql command client and seems to have already been processed/ignored illegal characters in some way. The underlying client/server protocol does return the bytes of the BLOB as is.

Peter-Jan Roes made changes - 2018-06-11 15:09

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:

{code:sql}
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`)));
{code}

This should produce something like:

{code:json}
{
  "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
      }
    }
  }
}
{code}

Please note the line:

{code}
"attached_condition": "table3.ObjectId = '?-?2}C????Z{ 2\0'"
{code}

The resulting JSON has been copied from the mysql command client and seems to have already been processed/ignored illegal characters in some way. The underlying client/server protocol does return the bytes of the BLOB as is.

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:

{code:sql}
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`)));
{code}

This should produce something like:

{code:json}
{
  "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
      }
    }
  }
}
{code}

Please note the line:

{code}
"attached_condition": "table3.ObjectId = '?-?2}C????Z{ 2\0'"
{code}

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.

Elena Stepanova made changes - 2018-07-17 16:38

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova added a comment - 2018-07-17 16:39

Thanks for the report. Reproducible as described, although I don't know what it is meant to produce instead, leaving it to psergey to decide.

Elena Stepanova added a comment - 2018-07-17 16:39 Thanks for the report. Reproducible as described, although I don't know what it is meant to produce instead, leaving it to psergey to decide.

Elena Stepanova made changes - 2018-07-17 16:39

Component/s		Admin statements [ 11400 ]
Component/s		JSON [ 13908 ]
Component/s		Optimizer [ 10200 ]
Fix Version/s		10.1 [ 16100 ]
Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Affects Version/s		10.1 [ 16100 ]
Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]
Assignee		Sergei Petrunia [ psergey ]

Sergei Golubchik made changes - 2019-03-29 12:04

Fix Version/s

10.4 [ 22408 ]

Julien Fritsch made changes - 2020-12-01 12:39

Fix Version/s

10.1 [ 16100 ]

Sergei Krivonos (Inactive) made changes - 2021-11-12 12:27

Assignee

Sergei Petrunia [ psergey ]

Sergei Krivonos [ JIRAUSER49805 ]

Sergei Krivonos (Inactive) made changes - 2021-11-12 12:40

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Sergei Krivonos (Inactive) made changes - 2021-12-06 14:40

Epic Link

MDEV-27176 [ 105740 ]

Sergei Krivonos (Inactive) made changes - 2021-12-06 15:11

Rank

Ranked higher

Sergei Golubchik made changes - 2021-12-06 21:36

Workflow

MariaDB v3 [ 87784 ]

MariaDB v4 [ 144484 ]

Sergei Petrunia made changes - 2021-12-15 19:35

Link

This issue relates to MDEV-25770 [ MDEV-25770 ]

Sergei Golubchik made changes - 2022-01-02 17:40

Assignee

Sergei Krivonos [ JIRAUSER49805 ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2022-01-02 17:40

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2022-01-02 17:40

Assignee

Sergei Golubchik [ serg ]

Sergei Petrunia [ psergey ]

Sergei Golubchik made changes - 2022-01-02 17:41

Epic Link

MDEV-27176 [ 105740 ]

Ralf Gebhardt made changes - 2022-08-04 08:41

Fix Version/s

10.2 [ 14601 ]

Julien Fritsch made changes - 2023-04-27 14:27

Fix Version/s

10.3 [ 22126 ]

People

Assignee:: Sergei Petrunia

Reporter:: Peter-Jan Roes

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2018-06-11 15:04

Updated:: 2023-04-27 14:27

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration