Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16462

EXPLAIN FORMAT=JSON produces illegal JSON text

    XMLWordPrintable

Details

    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

          Activity

            People

              psergei Sergei Petrunia
              PeterJanRoes Peter-Jan Roes
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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